Geek Answers Handbook

DATEDIFF () or BETWEEN for date ranges in SQL queries

I was recently told that using the BETWEEN method in SQL is somewhat unreliable, and therefore I have to use DATEDIFF() . However, another programmer told me that this is not so, and the BETWEEN method works brilliantly in all cases, as long as the date is formatted correctly.

Please, can someone solve this discussion by indicating which method is better and why?

Currently, my SQL date range is as follows:

 DATEDIFF(d,'01-Jan-1970',SIH.[Something_Date]) >= 0 AND DATEDIFF(d,'01-Jan-2013',SIH.[Something_Date]) <= 0

However, I would rather write it like this if I were sure that it was reliable:

 SIH.[Something_Date] BETWEEN '01-Jan-1970' AND '01-Jan-2013'

In this particular case, I am using MsSQL, however I have tagged MySQL as I would like to know if this is applicable here as well

+4
source share
1 answer

Your two requests are not equivalent. The datediff version will include all values ​​from 01-Jan-2013 regardless of time, while between versions only rows on 01-Jan-2013 , where the time is 00:00:00 will be included.

If you check the range and do not perform any calculations on the column, your query will be able to use the index on Something_Date and at the same time include all values ​​from 01-Jan-2013 regardless of the time part.

 where SIH.[Something_Date] >= '19700101' and SIH.[Something_Date] < '20130102'
+5
source

More articles:


All Articles