Prev: concatenating
Next: PIVOT data
From: SnapDive on 25 May 2010 10:46

I have a DateTimeOffset value, and want to get the DateTimeOffset
"floor" of the day (12:00am) and the "ceiling" of the day (11:59:59pm)
and am having trouble creating a concise statement that I can put into
a Where clause. Can anyone help with syntax on that?


Thanks.


From: Gert-Jan Strik on 25 May 2010 12:50
SnapDive wrote:
>
> I have a DateTimeOffset value, and want to get the DateTimeOffset
> "floor" of the day (12:00am) and the "ceiling" of the day (11:59:59pm)
> and am having trouble creating a concise statement that I can put into
> a Where clause. Can anyone help with syntax on that?
>
> Thanks.

For a continuous data type like datetime, there is no "ceiling". It
least not in my opinion. Because that would require dangerous
assumptions about the precision of the sample. For example, what would
you consider the ceiling of today? Would that be 2010-05-25T23:59 or
2010-05-25T23:59:59.997 or 2010-05-25T23:59:59.9999999 or anything in
between? If you get it wrong, or if the data types / precision doesn't
match you could get incorrect results.

I suggest that with datetimes, you always use the half open interval:
Start <= MyTime < End

A very efficient method of getting the "floor" of today is:

SELECT DATEADD(d, DATEDIFF(d, 0, CURRENT_TIMESTAMP), 0) AS Today

--
Gert-Jan
From: John Bell on 25 May 2010 13:33
On Tue, 25 May 2010 10:46:00 -0400, SnapDive
<SnapDive(a)community.nospam> wrote:

>
>I have a DateTimeOffset value, and want to get the DateTimeOffset
>"floor" of the day (12:00am) and the "ceiling" of the day (11:59:59pm)
>and am having trouble creating a concise statement that I can put into
>a Where clause. Can anyone help with syntax on that?
>
>
>Thanks.
>
Hi

I assume that just casting to date is what you want? When you want the
ceiling it is better to use a strict inequality and midnight rather
than round to some fraction of a second.

John
From: SnapDive on 25 May 2010 14:57
Thanks for the help, I got it!
:)

On Tue, 25 May 2010 18:33:42 +0100, John Bell
<jbellnewsposts(a)hotmail.com> wrote:

>On Tue, 25 May 2010 10:46:00 -0400, SnapDive
><SnapDive(a)community.nospam> wrote:
>
>>
>>I have a DateTimeOffset value, and want to get the DateTimeOffset
>>"floor" of the day (12:00am) and the "ceiling" of the day (11:59:59pm)
>>and am having trouble creating a concise statement that I can put into
>>a Where clause. Can anyone help with syntax on that?
>>
>>
>>Thanks.
>>
>Hi
>
>I assume that just casting to date is what you want? When you want the
>ceiling it is better to use a strict inequality and midnight rather
>than round to some fraction of a second.
>
>John

 | 
Pages: 1
Prev: concatenating
Next: PIVOT data