0

Can someone please help to get only last 6 months details. Tried with the answers which already mentioned in stackoverflow and mysql site. But didnt get the required output.

i tried with different combinations. but couldnt. 

mysql> select date from bo_rr;
+------------+
| date       |
+------------+
| 2013-06-14 |
| 2013-06-14 |
| 2013-06-14 |
| 2013-09-14 |
| 2013-09-14 |
| 2013-06-19 |
+------------+
6 rows in set (0.00 sec)

mysql> select date from bo_rr where date > date_sub(now(),interval 6 month);
+------------+
| date       |
+------------+
| 2013-06-14 |
| 2013-06-14 |
| 2013-06-14 |
| 2013-09-14 |
| 2013-09-14 |
| 2013-06-19 |
+------------+
6 rows in set (0.00 sec)

mysql> select date from bo_rr where date >= date_sub(now(),interval 6 month);
+------------+
| date       |
+------------+
| 2013-06-14 |
| 2013-06-14 |
| 2013-06-14 |
| 2013-09-14 |
| 2013-09-14 |
| 2013-06-19 |
+------------+
6 rows in set (0.00 sec)

mysql> select date from bo_rr where date < date_sub(now(),interval 6 month);
Empty set (0.00 sec)

mysql> select date from bo_rr where date <= date_sub(now(),interval 6 month);
Empty set (0.00 sec)

mysql> select date from bo_rr where date > date_sub(now(),interval -6 month);
Empty set (0.00 sec)

mysql> select date from bo_rr where date >= date_sub(now(),interval -6 month);
Empty set (0.00 sec)

mysql> select date from bo_rr where date < date_sub(now(),interval -6 month);
+------------+
| date       |
+------------+
| 2013-06-14 |
| 2013-06-14 |
| 2013-06-14 |
| 2013-09-14 |
| 2013-09-14 |
| 2013-06-19 |
+------------+
6 rows in set (0.00 sec)

mysql> select date from bo_rr where date <= date_sub(now(),interval -6 month);
+------------+
| date       |
+------------+
| 2013-06-14 |
| 2013-06-14 |
| 2013-06-14 |
| 2013-09-14 |
| 2013-09-14 |
| 2013-06-19 |
+------------+
6 rows in set (0.00 sec)
divakar.scm
  • 1,256
  • 5
  • 21
  • 32

1 Answers1

3

Since you have future records you need to set both start and end boundary conditions. You can conveniently do so with BETWEEN.

Try

SELECT date 
  FROM bo_rr 
 WHERE date BETWEEN CURDATE() - INTERVAL 6 MONTH AND CURDATE()

or

SELECT date 
  FROM bo_rr 
 WHERE date <= CURDATE() 
   AND date >= CURDATE() - INTERVAL 6 MONTH

Output:

+------------+
| date       |
+------------+
| 2013-06-14 |
| 2013-06-14 |
| 2013-06-14 |
| 2013-06-19 |
+------------+
peterm
  • 91,357
  • 15
  • 148
  • 157
  • You're more than welcome :) If it was what you were looking for please, consider to **[accept](http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work)** the answer. – peterm Jun 19 '13 at 09:55