Search code examples
mysqlsqldate-format

DATE_FORMAT two times in Where Clause doesn't work

I want to check if a dataset is older than the current month -1 day (so if it's the first of November it should still be older than October). This is my SQL:

SELECT *
FROM XY
WHERE DATE_FORMAT(calendar_day, '%Y-%m') <> DATE_FORMAT((CURRENT_DATE()-1, '%Y-%m');

But it doesn't work because of the second DATE_FORMAT. If I remove it, it works, but then it also compares the days and not the months. How do I solve this?

Solution

  • I want to check if a dataset is older than the current month -1

    Don't use DATE_FORMAT() on a column for this type of query. Keep all date functions on the "current date". Functions on columns impede optimization.

    I think this does what you want:

    SELECT *
FROM XY
WHERE calendar_day <= LAST_DAY(CURRENT_DATE() - interval 1 day - interval 1 month);