DATEDIFF()或BETWEEN用于SQL查询中的日期范围

问题描述：

我最近被告知，在SQL中使用BETWEEN方法有些不可靠，因此我应该使用DATEDIFF().但是，另一位程序员告诉我不是这种情况，只要日期格式正确，BETWEEN方法在所有情况下都能很好地发挥作用.

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?

目前我的日期范围SQL如下:

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'

在这种情况下，我使用的是MsSQL，但是我已经标记了MySQL，因为我想知道这是否也适用于此

答

您的两个查询不相同. datediff版本将包含01-Jan-2013中的所有值，而与时间无关，而介于两个版本之间的版本将仅包含01-Jan-2013上时间为00:00:00的行.

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.

如果检查范围并且不对列进行任何计算，则查询将能够使用Something_Date上的索引，并且同时包括01-Jan-2013中的所有值，而不考虑时间部分

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'

DATEDIFF()或BETWEEN用于SQL查询中的日期范围

相关推荐