How do I compare two datetime fields in SQL 2005?

Question

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?

Answer 1

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

Answer 2

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]