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 tim

Solution 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:

1. format both values (as varchar etc) using CONVERT : expensive for CPU, can't use index
2. use DATEDIFF/DATEPART to do the math - similar, but not quite as expensive
3. 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.

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 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.

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

Solution 2:

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