CODEWITHSUNDEEP
mysqlsqldatetime
red888
red888

Reputation: 31520

mysql query to select between previous and next month

Trying to select all days from the start of the previous month to the end of the next month:

USE test;

SELECT *  FROM  MyTable
WHERE col_date BETWEEN DATE_ADD(DATE_ADD(NOW(), INTERVAL -1 MONTH)) 
             AND DATE_ADD(DATE_ADD(NOW(), INTERVAL +1 MONTH))

Its saying my syntax in incorrect, but I saw snippet of the DATE_ADD function being used like this.

Upvotes: 0

Views: 1124

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You can use date_sub()

date_sub(now(), INTERVAL 1 MONTH)

So the query should be something as

USE test;
SELECT * FROM MyTable
WHERE 
col_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH)  AND DATE_ADD(NOW(), INTERVAL 1 MONTH)

Here how it looks like in mysql

mysql> select date_sub(now(), INTERVAL 1 MONTH) as previous_month , date_add(now(),INTERVAL 1 MONTH) as next_month;
+---------------------+---------------------+
| previous_month      | next_month          |
+---------------------+---------------------+
| 2014-05-06 22:27:35 | 2014-07-06 22:27:35 |
+---------------------+---------------------+
1 row in set (0.01 sec)

mysql> select NOW() - INTERVAL 1 MONTH as previous_month ,NOW() + INTERVAL 1 MONTH as next_month ;
+---------------------+---------------------+
| previous_month      | next_month          |
+---------------------+---------------------+
| 2014-05-06 22:28:39 | 2014-07-06 22:28:39 |
+---------------------+---------------------+

Upvotes: 1

Related Questions