# Problem with date and INTERVAL

Hi all.

I have problem with this query.
In my table mysql `tbl_d` the value of the field `myDate` is 2011-05-23.

``````
SELECT d.LOCK, d.myDate, x.dt
FROM tbl_d AS d

CROSS JOIN (
SELECT
CASE

#case 1
WHEN SUM(CASE WHEN MONTH(myDate) = MONTH(CURRENT_DATE)
AND YEAR(myDate) = YEAR(CURRENT_DATE) THEN 1 ELSE 0 END)
> 0 THEN CURRENT_DATE

#case 2
WHEN SUM(CASE WHEN MONTH(myDate) = MONTH(CURRENT_DATE -1)
AND YEAR(myDate) = YEAR(CURRENT_DATE) THEN 1 ELSE 0 END)
> 0 THEN CURRENT_DATE - INTERVAL 1 MONTH

#case 3
WHEN SUM(CASE WHEN MONTH(myDate) = MONTH(CURRENT_DATE -2)
AND YEAR(myDate) = YEAR(CURRENT_DATE) THEN 1 ELSE 0 END)
> 0 THEN CURRENT_DATE - INTERVAL 2 MONTH

#last case
ELSE CURRENT_DATE - INTERVAL 3 MONTH
END AS dt
FROM tbl_d) AS x

WHERE 1

AND MONTH(d.myDate) = MONTH(x.dt)
AND YEAR(d.myDate) = YEAR(x.dt)

GROUP BY d.LOCK
ORDER BY d.LOCK ASC

``````

The sql debug:
#case 1 —> 5 = 7 false;
#case 2 —> 5 = 6 false;
#case 3 —> 5 = 5 true;

Affected rows: 0
Time: 0.110ms

Why? Always in the #last case?
Thanks in advance, any help is very appreciated.

the value of the field `myDate` is 2011-05-23

Because we’re in july (month 7) and 7 - 2 = 5 ?

I’m sorry, I don’t understand… :S

7 - 2 = 5 … ok, are in #case 3 ( 5=5 )?

Why affected rows: 0 ?

If no exists a date with the current month, I need extend the time interval up to find a valid date…

you have this –

``````MONTH(CURRENT_DATE - 1)
``````

and i’m pretty sure you mean this –

``````MONTH(CURRENT_DATE) - 1
``````

check for yourself, just run this query –

``````SELECT CURRENT_DATE
, MONTH(CURRENT_DATE) AS mm
, MONTH(CURRENT_DATE - 1) AS x
, MONTH(CURRENT_DATE) - 1 AS y
``````

what do you get for x and y?

I need an reading update on MySQL … many thanks for your very appreciated help.
Regards.

``````
CURRENT_DATE	mm	x	y	LOCK	dt
2011-07-04	7	7	6	1	2011-05-04

``````