如何比较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

How can I do this?

我怎样才能做到这一点?

2 个解决方案

#1

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:

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

format both values (as varchar etc) using CONVERT : expensive for CPU, can't use index

使用CONVERT格式化两个值(作为varchar等):对CPU来说很昂贵,不能使用索引

use DATEDIFF/DATEPART to do the math - similar, but not quite as expensive

使用DATEDIFF / DATEPART进行数学运算 - 类似但不太昂贵

create a range to search between

创建一个范围来搜索

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

第三种选择几乎总是最有效的,因为它可以很好地利用索引,并且不需要大量的CPU。

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

例如,在上面,因为你的精度是秒*,我会使用:

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

then add a WHERE of the form:

然后添加表单的WHERE:

WHERE column_datetime >= @p_date AND column_datetime < @end

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.

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

[*=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]

[* =如果@p_date包含毫秒,你需要考虑更多关于是否通过DATEADD修剪这些ms,或做更小的范围等]

#2

I don't quite understand your problem, but DateDiff can be used to compare dates.

我不太了解你的问题,但DateDiff可用于比较日期。

#1