How do I compare two datetime fields in SQL Server 2005?

The question is unclear, but it looks like you are trying to do an equality match that does not return the expected rows, so I guess the problem is that milliseconds are problematic. There are several approaches here:

• format both values ​​(as varchar, etc.) using CONVERT: expensive CPU, can't use index
• use DATEDIFF / DATEPART to do the math - similar but not quite that expensive
• create a search range between

The third option is almost always the most efficient because it can use indexing efficiently and does not require CPU mass.

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

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

      

        
        
        
      

    

then add the WHERE of the form:

WHERE column_datetime >= @p_date AND column_datetime < @end

      

        
        
        
      

    

This will work best if you have a clustered pointer to column_datetime, but it will still work fine if you have a nonclustered index on column_datetime.

[* = if @p_date includes milliseconds, you need to think more about whether to trim those milliseconds after DATEADD

or make a smaller range, etc.]