SQLで先月という表現 - Matsubo Tech Blog

192 文字

1 分

SQLで先月という表現

2009-11-16

MySQL

/

SQL

SQLで先月という制約を書く方法。

col BETWEEN
date_format(adddate(now(), interval -1 month),'%y-%m-01')
AND
last_day(adddate(now(), interval -1 month));

以下、MySQLでの実験。現在は2009年11月16日。

mysql> select  '2009-10-16' between  DATE_FORMAT(adddate(now(), interval -1 month),'%Y-%m-01') and last_day(adddate(now(), interval -1 month)) as result;
+--------+
| result |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

mysql> select  '2009-10-31' between  DATE_FORMAT(adddate(now(), interval -1 month),'%Y-%m-01') and last_day(adddate(now(), interval -1 month)) as result;
+--------+
| result |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

mysql> select  '2009-10-01' between  DATE_FORMAT(adddate(now(), interval -1 month),'%Y-%m-01') and last_day(adddate(now(), interval -1 month)) as result;
+--------+
| result |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

mysql> select  '2009-11-01' between  DATE_FORMAT(adddate(now(), interval -1 month),'%Y-%m-01') and last_day(adddate(now(), interval -1 month)) as result;
+--------+
| result |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)

mysql> select  '2009-09-31' between  DATE_FORMAT(adddate(now(), interval -1 month),'%Y-%m-01') and last_day(adddate(now(), interval -1 month)) as result;
+--------+
| result |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)

mysql>