How to compare just the Date portion of DateTime fields

What's the best way to compare just the Date portions of datetime
fields (ignore the time)
lateFlag = case
when TargetEndDate is null then 'N'
when TargetEndDate < getutcdate() then 'Y'
else 'N'
end
RonUse the CONVERT function to get the desired component of a date time.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"RonL" <sal_paradise_93@.yahoo.com> wrote in message
news:1145850793.160785.158820@.t31g2000cwb.googlegroups.com...
> What's the best way to compare just the Date portions of datetime
> fields (ignore the time)
> lateFlag = case
> when TargetEndDate is null then 'N'
> when TargetEndDate < getutcdate() then 'Y'
> else 'N'
> end
>
> Ron
>|||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
RonL wrote:

>What's the best way to compare just the Date portions of datetime
>fields (ignore the time)
>lateFlag = case
> when TargetEndDate is null then 'N'
> when TargetEndDate < getutcdate() then 'Y'
> else 'N'
> end
>
>Ron
>
>|||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
SriSamp wrote:

>Use the CONVERT function to get the desired component of a date time.
>--
>HTH,
>SriSamp
>Email: srisamp@.gmail.com
>Blog: http://blogs.sqlxml.org/srinivassampath
>URL: http://www32.brinkster.com/srisamp
>"RonL" <sal_paradise_93@.yahoo.com> wrote in message
>news:1145850793.160785.158820@.t31g2000cwb.googlegroups.com...
>
>
>|||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
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:etjXwS1ZGHA.1192@.TK2MSFTNGP04.phx.gbl...
> Use the CONVERT function to get the desired component of a date time.
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "RonL" <sal_paradise_93@.yahoo.com> wrote in message
> news:1145850793.160785.158820@.t31g2000cwb.googlegroups.com...
>|||Great. Thanks.
Ron