将SQL Server日期时间字段转换为仅与索引查找比较日期部分 [英] Convert SQL server datetime fields to compare date parts only, with indexed lookups

问题描述

我在SQL Server中的'inter'查询中使用的每个日期字段上都执行了一个 convert（varchar，datefield，112），以确保我只记录日期，而不是基于datetime字段的时间部分丢失任何内容。

I've been doing a convert(varchar,datefield,112) on each date field that I'm using in 'between' queries in SQL server to ensure that I'm only accounting for dates and not missing any based on the time part of datetime fields.

现在，我听说转换器不可索引，在SQL Server 2005中有更好的方法来比较查询中日期时间的日期部分，以确定日期是否落在一个范围内。

Now, I'm hearing that the converts aren't indexable and that there are better methods, in SQL Server 2005, to compare the date part of datetimes in a query to determine if dates fall in a range.

什么是最佳可索引，做这样的方法：

What is the optimal, indexable, method of doing something like this:

select * from appointments
where appointmentDate>='08-01-2008' and appointmentDate<'08-15-2008'

推荐答案

将数字类型转换为字符串值（一种类型的Boxing）不是执行正在寻找的最佳方法。它不是真的关于索引能力，因为实际的列类型是日期时间。

Converting numeric types to string values (a type of Boxing) is not the best performing method of doing what you are looking for. Its not really about index-able, because the actual column type is date time.

如果您正在寻找日期的最佳方式查询，则您的示例是正确的，但您可能需要考虑MSSQL中的3 ms精度差异。这可能意味着有一天的记录可能会在另一天的结果中出现。

If you are looking for the best way query for dates, then your example is right, but you may want to take into account the 3 ms precision difference in MSSQL. It can mean that records from one day can show up in another day's result.

这个

select * from appointments where appointmentDate>='08-01-2008' and appointmentDate<'08-15-2008'

应该是这个

select * from appointments where appointmentDate>='08-01-2008' and appointmentDate<='08-14-2008 23:59:59.996'

这篇关于将SQL Server日期时间字段转换为仅与索引查找比较日期部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！