Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
2.2k views
in Technique[技术] by (71.8m points)

mysql - DATEDIFF() or BETWEEN for Date Ranges in SQL Queries

I have recently been informed that the use of the BETWEEN method in SQL is somewhat unreliable, and I should therefore be using DATEDIFF(). However, another programmer has informed me this is not the case and the BETWEEN method works brilliantly in all cases as long as the date is formatted correctly.

Please could someone settle this debate by stating which method is better and why?

At the moment my date range SQL looks like this:

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

However, I would much rather write it like this if I can be sure it is 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 applies here as well

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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

If you check against the range and don't do any calculations on the column, your query will be able to use a 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'

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...