如何比较 SQL Server 2005 中的两个日期时间字段? [英] How do I compare two datetime fields in SQL Server 2005?

问题描述

DECLARE  @p_date DATETIME
SET      @p_date = CONVERT( DATETIME, '14 AUG 2008 10:45:30',?)

SELECT   *
FROM     table1
WHERE    column_datetime = @p_date

我需要比较日期时间，例如:

I need to compare date time like:

@p_date=14 AUG 2008 10:45:30
column_datetime=14 AUG 2008 10:45:30

我该怎么做?

推荐答案

问题不清楚，但看起来您正在尝试执行不返回您期望的行的相等匹配，所以我猜问题是毫秒是有问题的.这里有几种方法:

The question is unclear, but it looks like you are trying to do the equality match that isn't returning the rows you expect, so I'm guessing that the problem is that the milliseconds are being problematic. There are several approaches here:

1. 格式化两个值(作为 varchar等)使用 CONVERT :昂贵的CPU，不能使用索引
2. 使用 DATEDIFF/DATEPART 来做数学 - 类似，但不完全昂贵的
3. 创建一个范围来搜索

第三个选项几乎总是最有效的，因为它可以很好地利用索引，并且不需要大量的 CPU.

The 3rd option is almost always the most efficient, since it can make good use of indexing, and doesn't require masses of CPU.

例如，在上面，由于您的精度是秒*，我会使用:

For example, in the above, since your precision is seconds*, I would use:

DECLARE @end datetime
SET @end = DATEADD(ss,1,@p_date)

然后添加表单的WHERE:

then add a WHERE of the form:

WHERE column_datetime >= @p_date AND column_datetime < @end

如果您在 column_datetime 上有聚集索引，这将最有效，但如果您在 column_datetime 上有非聚集索引，这仍然可以正常工作.

This will work best if you have a clustered index on column_datetime, but should still work OK if you have a non-clustered index on column_datetime.

[*=如果@p_date 包含毫秒，您需要更多地考虑是否通过 DATEADD 修剪这些毫秒，或者做更小的范围等]

[*=if @p_date includes milliseconds you'd need to think more about whether to trim those ms via DATEADD, or do a smaller range, etc]

这篇关于如何比较 SQL Server 2005 中的两个日期时间字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！