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
- use DATEDIFF/DATEPART to do the
math - similar, but not quite as
expensive
- 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]