CODEWITHSUNDEEP
sql
Rory Lester
Rory Lester

Reputation: 2918

SQL query selecting between two dates

I want to select records between two dates - a startDate and endDate (they are date/time format in sql). I have the following sql query but it does not work, could someone tell me what I'm doing wrong? 

SELECT * 
FROM house 
WHERE startDate >= '2012/02/22 00:00:00' AND endDate <= '2012-02-25 00:00:00'

Upvotes: 3

Views: 8095

Answers (4)

Himanshu
Himanshu

Reputation: 3970

I guess thats type casting issue the reason why it din work because the input you are matching in the where clause is different that is the column is of date or datetime type and you are matching with a manual string format either use to_char on the left side of where to match the format on the right side or use to_date() on right side.

   SELECT * 
    FROM house 
   WHERE 
    to_char(startDate, 'YYYY/MM/DD
      24hh:mm:ss')>= 
     '2012/02/22  00:00:00' 
         AND to_char(endDate, 
      'YYYY/MM/DD
      24hh:mm:ss') <= '2012-02-25 
        00:00:00'

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

Do you want all rows that startDate is '2012-02-22' or later and endDate is '2012-02-22' or previous? Then, use this:

SELECT * 
FROM house 
WHERE startDate >= '2012-02-22' 
  AND endDate < '2012-02-26'           --- notice the `<`, not `<=`
                                       --- and the `day+1`

When using dates with SQL products, better use this format in queries and statements: '20120222' or this (which I find easier to read: '2012-02-22'.

Using slashes like '2012/02/22' or any other order than Year-Month-Day is not recommended.

There's no need to include the time part. '2012-02-22 00:00:00' is the same as '2012-02-22'.

Using endDate <= '2012-02-25 00:00:00' means that any row with date 25nd of Feb. 2012 but time after midnight ('00:00:00') will not match the condition. If you want those rows, too, use endDate < '2012-02-26' instead.

You could use DATE(endDate) <= DATE('2012-02-25 00:00:00') or DATE(endDate) <= '2012-02-25' but these conditions are "un-sargable", so your queries will not be able to use an index on endDate.

Upvotes: 2

northpole
northpole

Reputation: 10346

I would suggest converting the dates to a datetime and comparing them as well as keeping the date standard and consistent. Something like:

"SELECT * 
FROM house 
WHERE DATE(startDate) >= DATE('2012-02-22 00:00:00') 
  AND DATE(endDate) <= DATE('2012-02-25 00:00:00')"

NOTE: I assumed your startDate and endDate were of the same format as the strings your provided.

Upvotes: 4

Benoit
Benoit

Reputation: 79185

There is the builtin STR_TO_DATE function in MySql that takes same format mask as date_format.

start_date >= str_to_date('2012/02/22 00:00:00','%Y/%m/%d %h:%i:%s)

Upvotes: 0

Related Questions