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