CODEWITHSUNDEEP
mysqlsql
Betafish
Betafish

Reputation: 1262

SQL query for x- dates before and after a given date range

I am trying to extract x-days before and after a given date range. I have formulated the query as follows

SELECT a,b,date FROM table.test 
 WHERE date < "2012-09-07" - INTERVAL 2 DAY 
   AND date > "2012-09-08"  + INTERVAL 2

The other approach which was NOT BETWEEN method also. None of them seems to be giving me right answer. The date is of type varchar(30) DEFAULT NULL within the database

Sample Data 

a b date
2 4 2012-09-07
3 2 2012-09-05
5 3 2012-09-08
7 4 2012-09-07
8 5 2012-09-06
9 6 2012-09-07
3 7 2012-09-09

What I am looking for it the following:

a b date
3 2 2012-09-05
3 7 2012-09-09

The date is of type varchar(30) DEFAULT NULL within the database

Upvotes: 0

Views: 968

Answers (2)

Trinimon
Trinimon

Reputation: 13967

Go for this:

SELECT a,b,date 
  FROM table.test 
 WHERE date < str_to_date('2012-09-07', '%Y-%m-%d')  - INTERVAL 2 DAY 
    OR date > str_to_date('2012-09-08', '%Y-%m-%d')  + INTERVAL 2 DAY;

Mainly, the AND needs to replaced by OR. I'd prefer an expicit (date) type conversion as well.

Update: re-reading your question carefully, the statement above might not be the one you are looking for. If Gordon go it right and if you want to use BETWEEN you might go for:

SELECT a,b,date 
  FROM table.test 
 WHERE date BETWEEN str_to_date('2012-09-07', '%Y-%m-%d')  - INTERVAL 2 DAY AND
                    str_to_date('2012-09-08', '%Y-%m-%d')  + INTERVAL 2 DAY 
   AND date NOT BETWEEN str_to_date('2012-09-07', '%Y-%m-%d') AND
                        str_to_date('2012-09-08', '%Y-%m-%d');

This is basically the same.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You just have your comparisons backwards:

SELECT a, b, date
FROM table.test 
WHERE (date >= '2012-09-07' - INTERVAL 2 DAY AND
       date < '2012-09-07'
      ) OR
      (date > '2012-09-08' AND
       date <= '2012-09-08' + INTERVAL 2
      );

I'm not sure if you want >/< or >=/<=.

Upvotes: 1

Related Questions