Sequelize How compare year of a date in query

Question

I'm trying to make this query:

SELECT * FROM TABLEA AS A WHERE YEAR(A.dateField)='2016'

How can I perfome this query above in sequelize style?

TABLEA.findAll({
      where:{}//????
     }

Thanks!

Answer 1

TABLEA.findAll({
  where: sequelize.where(sequelize.fn('YEAR', sequelize.col('dateField')), 2016)
 });

You have to use .where here, because the lefthand side of the expression (the key) is an object, so it cannot be used in the regular POJO style as an object key.

If you want to combine it with other conditions you could do:

TABLEA.findAll({
  where: {
    $and: [
      sequelize.where(sequelize.fn('YEAR', sequelize.col('dateField')), 2016),
      { foo: 'bar' }
    ]
  }
 });

https://sequelize.org/v3/docs/querying/#operators

Answer 2

you have to add the date_part function to your query:

sequelize.where(sequelize.fn("date_part",'year',sequelize.col('dateField')), 2020)