# Problem with date and INTERVAL

• Jul 4, 2011, 07:14
Miguel61
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.

Code:

```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.
• Jul 4, 2011, 07:21
guido2004
Quote:

the value of the field `myDate` is 2011-05-23
Because we're in july (month 7) and 7 - 2 = 5 ?
• Jul 4, 2011, 07:25
Miguel61
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...
• Jul 4, 2011, 07:45
r937
you have this --
Code:

`MONTH(CURRENT_DATE - 1)`
and i'm pretty sure you mean this --
Code:

`MONTH(CURRENT_DATE) - 1`
check for yourself, just run this query --
Code:

```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?

:cool:
• Jul 4, 2011, 07:56
Miguel61
I need an reading update on MySQL ... many thanks for your very appreciated help.
Regards.

Code:

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