How do I retrieve records from the database using SQL's BETWEEN clause? My goal is to select records that fall within a specified startDate and endDate. If the record exists within this range, it should return true; otherwise, it should return false.
//form
formData = {
"startDate": "14/04/2022 09:41 PM",
"duration": 3600000,
"assignedTo": "441017201|441017210",
}
// current code
var checkExistingEvent = (assignedTo, startDate, end) => {
var assignees = assignedTo.split("|")
var userFilters = assignees.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 validateUserExistsEvent = 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;
}
Database Record: