'How to select record in the database using BETWEEN in sql?

How to select record in the database using BETWEEN in sql? what I want here is to select record within the startDate and endDate. if the record is exists within the range of startDate and endDate it will return true and if not it will return false

//form

formData = {
    "startDate": "14/04/2022 09:41 PM",
    "duration": 3600000,
    "assignedTo": "441017201|441017210",
}

// current code

var hasExistingEvent = (assignedTo, startDate, end) => {
    var assigne = assignedTo.split("|")
    var userFilters = assigne.map((user) => {
        return `Assigned_To_Ids LIKE '%${user}%'`;
    }).join(" OR ");
    return  new Promise((resolve, reject) => {
        .....,("SELECT Assigned_To_Ids, startDate FROM Event WHERE ("+ userFilters +") AND (startDate BETWEEN '" + moment(startDate).format() + "' AND '"+ moment(end).format() + "')", 100, function(data){
            return resolve(data.length ==0)
        })
    })
}

var validatedUserExistsEvent = async(formData) => {
    var {  assignedTo, startDate, duration } = formData
    var dd_startDate = moment(startDate, 'DD/MM/YYYY HH:m A').toDate()
    var end = new Date(dd_startDate);
        end = new Date(end.setTime(end.getTime() + duration));
    var hasExisting = await _data.hasExistingEvent(assignedTo, dd_startDate, end);
    return hasExisting;
}

record from database

enter image description here

javascriptmysqlsqlmomentjs


Solution 1:[1]

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN startDate AND endDate;

So basically: You select the column names that you want from the table. Where the column that contains your date is between startDate and endDate.

Solution 2:[2]

you can create sp for this operation or use sql command like this

select case when exists (select * from Ls2 where date between @startdate and @EndDate) then 1 else 0 end

Solution 3:[3]

For Example, Below query fetch data between March 1 to March 30 2022 using Between.

SELECT * FROM table_name WHERE column_name BETWEEN startDate BETWEEN '2022-03-01 00:00:00' AND '2022-03-31 23:59:59';

If your table has data satisfying this condition, there will be some rows in array otherwise it will be empty.

So based on that, you can set your variable as true and false.

Solution 4:[4]

METHOD 1: In case if you want to get result as true or false on the basis of SQL condition, then you can use SELECT CASE of sql

SELECT
  CASE WHEN EXISTS 
  (
        SELECT Assigned_To_Ids 
        FROM events 
        WHERE startDate BETWEEN someStartDate and someEndDate 
  )
  THEN 'TRUE'
  ELSE 'FALSE'

END

here someStartDate, someEndDate are variables which you can replace accordingly.

METHOD 2:

You can write sql query to return 0 or positive value depending on whether records are present or not afterwards with simple check in function you can return the value as true or false. In that case sql query will be

SELECT count(Assigned_To_Ids) 
FROM events 
WHERE startDate BETWEEN someStartDate and someEndDate

Solution 5:[5]

SELECT CASE WHEN EXISTS ( SELECT COLUMN_NAME FROM events WHERE startDate BETWEEN @startDate and @enddate ) THEN 'TRUE' ELSE 'FALSE'

Solution 6:[6]

Not exactly sure what's going on, but I'm guessing you are looking for this:

`SELECT Assigned_To_Ids, startDate 
FROM Event
WHERE ${userFilters} 
AND (
  startDate < ${moment(startDate).format()}
  AND startDate > ${moment(end).format()}
)`

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Ayak
Solution 2 hossein andarkhora
Solution 3 charmi zala
Solution 4 VipulSingh
Solution 5 Ashutosh Mawalkar
Solution 6 Michael Grove

Related Questions