javascript - Mimic where [condition] between [colA] and [colB] sequelize

Question

Looking for assistance in replicating the following mssql operation in sequelize. We have a table with a valid_from and valid_to columns and I need to get todays current date and only return the records that are fall based on that.

I am looking to utilize the .findByPK(), or .findAll() methods in sequelize

SELECT * FROM [table] WHERE GETUTCDATE() BETWEEN f.valid_from AND f.valid_to 

I have found the following posts and items with no luck. As they seem to specify two di9ffernt dates between the same column. I need to compare the current UTCDATE between two different columns

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

Sequelize query - compare dates in two columns

I was able to simulate but would still like to know if anyone knows how to do the same thing using the between operator

const now = new Date();
            return await models.Form.findAll({
                where: {
                    valid_from: {
                        [Op.lte]: Sequelize.cast(now, 'DATETIMEOFFSET')
                    },
                    [Op.and]: {
                        validTo: {
                            [Op.gte]: Sequelize.cast(now, 'DATETIMEOFFSET')
                        }
                    }
                }
            });

question from:https://stackoverflow.com/questions/65886557/mimic-where-condition-between-cola-and-colb-sequelize

Answer 1

You can write this query using sequelize.where() to generate the WHERE statement for the BETWEEN operation. For the current timestamp you need to call GETUTCDATE() which we can do via sequelize.fn().

const forms = await models.Form.findAll({
  where: {
    // create where condition for GETUTCDATE()
    sequelize.where(sequelize.fn('GETUTCDATE'), {
      // BETWEEN valid_from AND validTo
      [Op.between]: [
        sequelize.col('valid_from'), 
        sequelize.col('validTo')
      ],
    }),
  },
});

This will generate SQL like:

SELECT * FROM `form`
WHERE GETUTCDATE() BETWEEN `valid_from` AND `validTo`

note that you have underscored valid_from and camelCase validTo in your example.