Sequelize Query to find all records that falls in between date range

I have a model with columns:

from: { type: Sequelize.DATE }
to: { type: Sequelize.DATE }

I want to query all records whose either from OR to falls in between the date ranges : [startDate, endDate]

Tried Something Like:

const where = {
    $or: [{
        from: {
            $lte: startDate,
            $gte: endDate,
        },
        to: {
            $lte: startDate,
            $gte: endDate,
        },
    }],
};

Something Like:

SELECT * from MyTable WHERE (startDate <= from <= endDate) OR (startDate <= to <= endDate

The solution which works for me is this:-

// here startDate and endDate are Date objects
const where = {
    from: {
        $between: [startDate, endDate]
    }
};

For reference to know more about operators:- http://docs.sequelizejs.com/en/latest/docs/querying/#operators

Note: In MYSQL between comparison operator is inclusive, which means it is equivalent to the expression (startDate <= from AND from <= endDate).

Try this condition what I think you are asking will do so.

For New Version Of Sequelize:

const where = {
    [Op.or]: [{
        from: {
            [Op.between]: [startDate, endDate]
        }
    }, {
        to: {
            [Op.between]: [startDate, endDate]
        }
    }]
};

OR as your code structure:

const where = {
    $or: [{
        from: {
            $between: [startDate, endDate]
        }
    }, {
        to: {
            $between: [startDate, endDate]
        }
    }]
};

For more info you can follow this Sequelize official documents

step - 1 require the Op from SEQUELIZE

const { Op } = require('sequelize');

step - 2 declare two constants :

const startedDate = new Date("2020-12-12 00:00:00");
const endDate = new Date("2020-12-26 00:00:00");

step - 3

table.findAll({where : {"fieldOfYourDate" : {[Op.between] : [startedDate , endDate ]}}})
.then((result) =>  res.status(200).json({data : result}))
.catch((error) =>  res.status(404).json({errorInfo: error}))

I agree with @VigneshSundaramoorthy's comment that even if [Op.between] works with date strings, the type definitions suggest this usage isn't officially supported. The operation wants array of numbers, and at least as at 28 Dec 2021, the documentation has no examples of using Op.between for a date range (only for number ranges).

However, we can achieve the same result in a type-compliant way, which also aligns with the Sequelize documentation:

const where = {
  "date_field": {
    [Op.and]: {
      [Op.gte]: startOfDateRange,
      [Op.lte]: endOfDateRange
    }
  }
}

This has also has the slight advantage of being more explicit (that the search is inclusive) rather than relying on dialect behaviour for BETWEEN.