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?
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]
I don't quite understand your problem, but DateDiff can be used to compare dates.