Search This Blog

Monday, March 14, 2011

[Mysql] A common mistake when using CURDATE

An example of one of most common mistake while using CURDATE is

select DATE(creationdate) from tablename where DATE(creationdate)=CURDATE()-1 limit 3;

output of CURDATE()-1 is as follows


mysql> select CURDATE()-1;
+-------------+
| CURDATE()-1 |
+-------------+
|    20110313 |
+-------------+
1 row in set (0.00 sec)

but it should be something like this

mysql> select CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2011-03-14 |
+------------+
1 row in set (0.00 sec)

basically, in the format YYYY-MM-DD

and hence the correct way of using CURDATE is as follows



mysql> select DATE_SUB(CURDATE(),INTERVAL 1 DAY);
+------------------------------------+
| DATE_SUB(CURDATE(),INTERVAL 1 DAY) |
+------------------------------------+
| 2011-03-13                         |
+------------------------------------+
1 row in set (0.00 sec)

1 comment: