How to compare just the Date portion of DateTime fields -- sql server programming

Mike Hodgson
4/24/2006 12:00:00 AM

Or better yet a couple datetime functions:

select dateadd(d, datediff(d, 0, current_timestamp), 0)

so the OP's snippet of code would be something like

lateFlag =
case
when TargetEndDate is null then 'N'
when dateadd(d,datediff(d,0,TargetEndDate),0) <
dateadd(d,datediff(d,0,getutcdate()),0) then 'Y'
else 'N'
end

--
*mike hodgson*
http://sqlnerd.blogspot.com

[quoted text, click to view]

Colin Dawson
4/24/2006 12:00:00 AM

Don't use CONVERT for this. Use the DateAdd and DateDiff version, (it's
already been posted twice, so I won't repeat)

The reason for this is that Convert writes the result to a memory page.
This means SLOW AND EXPENSIVE! Using the DateAdd and DateDiff functions
does not result in a page write, it only uses a small amount of CPU whilst
running the functions, and there is no conversion of datatypes taking place.

Regards

Colin Dawson
www.cjdawson.com

[quoted text, click to view]

Steve Kass
4/24/2006 12:10:18 AM

If you want a condition that tells if TargetEndDate has a date
part before the date part of getutcdate(), you can do it while
still taking advantage of any useful index on TargetEndDate
by checking whether TargetEndDate with its time part is
before the date-only part of getutcdate() this way:

TargetEndDate < dateadd(day,datediff(day,0,getutcdate()),0)

The time part of TargetEndDate can't change whether it
is before a date-only value or not.

Steve Kass
Drew University

[quoted text, click to view]

RonL
4/24/2006 6:41:59 PM

Great. Thanks.

Ron

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : How to compare just the Date portion of DateTime fields