Date Comparison in Stored Procedure

Hello,

I want to compare 2 datetime but only on Date part, excluding Time .

For example

Date 1 = 07/01/2004 14:00:00
Date 2 = 07/01/2004 16:00:00

I want have true for Date 1 = Date 2

How can I do ?

Excuse my bad EnglishYou will have to use CONVERT with appropriate style that strips the time part and compare like:

if convert(char(8), @.date1, 112) = convert(char(8), @.date1, 112)

The style 112 is the ISO unseparated string format YYYYMMDD. You can do the same against a column in a table also. If the date column has an index then you can use a search condition like below to make efficient use of it since using CONVERT directly will not use the index.

-- restrict to all time values within a day
where datecol >= convert(char(8), @.date, 112)
and datecol < convert(char(8), @.date + 1, 112)|||

just cast as int

example:

declare @.date1 datetime
declare @.date2 datetime
set @.date1 = getdate()
set @.date2 = dateadd(mi, 1, @.date1)
select @.date1 [Date 1], @.date2 [Date 2],
case @.date1
when @.date2 then 'Yes!!!'
else 'No!!!'
end [Same DateTime?],
case cast(@.date1 as int)
when cast(@.date2 as int) then 'Yes!!!'
else 'No!!!'
end [Same Date?]

|||

I could not understand the below code given by you in your post...

where datecol >= convert(char(8), @.date, 112)
and datecol < convert(char(8), @.date + 1, 112)

will this not give a conversion error, if datecol is a datetime column ?

Anand