Mysql CURDATE() -1

Question

Please, I don't understand why this command returns 00 April.

Today is 1st April 2015, and executing:

mysql> SELECT CURDATE() -1;

it returns:

| 20150400 |

+--------------+

Why CURDATE-1 doesn't return 31st March?,

00 April is an error, please it would be great if somebody could explain it.

Next, on 2nd April it works well, also for the rest of the days within the month:

mysql>

select CURDATE() -1;

|     20150401 |

This function seems is not able to operate with +x / -x out of the current month scope.

This should be highlighted in the reference guide.

BTW, I'vereplaced it with this new function:

 SELECT SUBDATE(CURDATE(),1)

Thanks.

Answer 1

You need to use CURDATE() - INTERVAL 1 DAY.

What are you doing with CURDATE() - 1, is treating the result of CURDATE() as an integer, not a date.

So on April 1st, CURDATE() - 1 = 20150401 - 1 = 20150400, which is clearly an invalid date.

MySQL can't intuit this for you, because with -1 it doesn't know if you mean one less day, one less month, one less year, etc.

Using MySQL's date intervals as above is the correct way to do it.

Answer 2

You can use:

SELECT SUBDATE(CURDATE(), 1);

or

SELECT CURDATE() - INTERVAL 1 DAY;

or

SELECT SUBDATE(CURDATE(), INTERVAL 1 DAY);

or

SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY);