两个日期时间字段之间的SQL Server，不能正常工作 [英] SQL Server between two datetime fields, not working correctly

问题描述

SQL Server 2005:

SQL Server 2005:

以下视图

  SELECT CONVERT(VARCHAR(20), keyedtimestamp, 101) as KeyedDate
    FROM TMSSTATFILE_STATS a                                                                
   WHERE (CONVERT(VARCHAR(20), a.KeyedTimestamp, 101) BETWEEN '03/01/2011' And '03/31/2011')  
ORDER BY KeyedDate

给出关键日期为2011年3月2日至2011年3月31日的结果.

Results are given for keyed dates 3/2/2011 to 3/31/2011.

如果我将第一个日期更改为2011年3月00日

If I change the first date to 03/00/2011

  SELECT CONVERT(VARCHAR(20), keyedtimestamp, 101) as KeyedDate
    FROM TMSSTATFILE_STATS a                                                                
   WHERE (CONVERT(VARCHAR(20), a.KeyedTimestamp, 101) BETWEEN '03/00/2011' And '03/31/2011')  
ORDER BY KeyedDate

它现在提供日期为2011年3月1日至2011年3月31日的数据

it now gives data for dates 3/1/2011 to 3/31/2011

KeyedTimestamp字段为DateTime，并且存在与这些记录关联的时间.记录了2011年3月31日的所有记录.我知道我可以通过在第二个日期之间提供最大时间来代替，所以我不是在寻找替代where子句，而是要理解为什么它忽略了第一个记录，即使它包含了那些从31日开始.

The KeyedTimestamp field is DateTime and there are times associated with these records. All records for 3/31/2011 are accounted for. I know I can do this instead by supplying the max time in the second date in between, so I'm not looking for an alternative where clause, but rather an understanding of why it's ignoring the records from the first even though its incorporating the ones from the 31st.

几乎就像是在检查3/1/2011 23:59:59，我希望我可以在只关心日期而不是时间的情况下取消这种检查

Its almost as if its checking for 3/1/2011 23:59:59, I was hoping I could eliminate this kind of check where I only care about the date, not the time

推荐答案

通过不使用BETWEEN避免在列上使用函数.函数意味着将永远不会使用任何索引

Avoid a function on the column by not using BETWEEN. A function mean any index will never be used

WHERE
    a.KeyedTimestamp >= '20110301' AND a.KeyedTimestamp < '20110401'  

并且 SQL Server 2008之前的版本yyyymmdd 是唯一安全日期格式.

这篇关于两个日期时间字段之间的SQL Server，不能正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！