DateTime: Zeroing the time portion

Hi,

Any idea how I can zeroize the time part of a DateTime value?
I have a script which retrieves the current date and adds 4 hours to it like so:-

declare @Date datetime;
set @Date = dateadd(hour, 12, getdate());

The trouble is that after this the value of @Date might be equal to:

2004-05-09 12:15:17.793

but the field value that I need to compare this to later on (in a select) has no time part, ie:-

2004-05-09 00:00:00.000


Any idea how I could clear the time part of @Date so the comparison would succeed???

Many Thanks

dex

You can use the date funtions...here's a snip that will show you what it will output. You can possibly use it in your compare criteria.

...or, I've had luck w/being able to extract the yr, mo, day (no min.sec.ms) using the LEFT function, but I think my dates are smallDateTime...1st give it a shot w/getDate(), then try it w/your @Date and see what you get.

...that may spit out precision to the day, and leave out the time stuff.

LEFT(getDate(),11) leaves you at the mercy of the default date display format

the "best practice" method of zeroing the time portion of a datetime value is

cast(convert(char(10),datetimecol,120) as datetime)

however, i should warn you, if you perform a comparison like function(datetimecol) comparisonoperator value, chances are good the optimizer will ignore an index on datetimecol

what your query should look like is datetimecol comparisonoperator function(value)

for example, if i were looking for all datetimecol values from yesterday, i would write

ahhh...ok, admittingly I never knew for fact, but had suspected, about the display format. Do you know where in Server I can go to take a look @ that and what options there are?

Also Rudy, do you mind if I take that code you posted and play around w/it?

server options? sorry, i are not a dba

play around with my code, please

i think there may be an extra parenthesis at the end, the result of hasty cuttin&pastin

I meant in SQL Server, not sure if you thought I meant otherwise. Anyway, thx man.

of course i thought that you meant sql server

what did you think i thought you meant?

this is the sql server forum, after all

I don't know, nevermind, I'm complicated...sorry