CODEWITHSUNDEEP
mysqlsqldate-format
beld
beld

Reputation: 81

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?

Upvotes: 0

Views: 109

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

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);

Upvotes: 5

Fahmi
Fahmi

Reputation: 37473

Try this using year and month function:

SELECT *
FROM XY
WHERE (year(calendar_day) <> year(CURRENT_DATE()-1)) 
and (month(calendar_day)<>month(CURRENT_DATE()-1))

Upvotes: 0

Related Questions