Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to write a query that groups records based on the local date part only of a UTC datetime field.

For example, if my table contains 10/19/2012 2:00:00, then it should get grouped as 10/18/2012, since my local time is EST (-5h) and I'm only interested in the date portion of the field.

I know I can use DateAdd(day, DateDiff(day, 0, MyDate), 0) to get the date part only from the datetime field, and I can use DateAdd(minute, DateDiff(minute, GetUtcDate(), GetDate()), MyUtcDate) to convert a UTC datetime to a local date time.

But combining the two is seriously offending me.

Is there a better way than this to get just the Date part of a UTC DateTime field, converted to local time, in SQL Server 2005?

  SELECT DateAdd(day, DateDiff(day, 0, DateAdd(minute, DateDiff(minute, GetUtcDate(), GetDate()), MyUtcDate)), 0)
       , Count(*)
    FROM MyTable
GROUP BY DateAdd(day, DateDiff(day, 0, DateAdd(minute, DateDiff(minute, GetUtcDate(), GetDate()), MyUtcDate)), 0)
share|improve this question
    
I don't have an answer, but MINUTE should be granular enough. –  Aaron Bertrand Oct 19 '12 at 19:41
    
I don't have 2005 to play with, but does it support using CROSS APPLY to alias a function like this? –  JNK Oct 19 '12 at 19:44
    
@AaronBertrand Yes, minute probably will perform better than second. Updated my question :) –  Rachel Oct 19 '12 at 19:44
    
@JNK I think so, but I have no clue what I'd be CROSS APPLYing :) –  Rachel Oct 19 '12 at 19:45
    
SELECT FieldName FROM <table> CROSS APPLY (SELECT <calculation) AliasName(FieldName) GROUP BY FieldName –  JNK Oct 19 '12 at 19:46
show 1 more comment

2 Answers 2

up vote 3 down vote accepted

If you're not averse to having a function do the dirty work, this helps make the statement cleaner:

CREATE FUNCTION LocalDateFromUTCTime
(
    @UTCDateTime datetime
)
RETURNS datetime
BEGIN
    DECLARE @diff int;
    SET @diff = datediff(hh,GetUTCDate(), GetDate());
    RETURN DATEADD(day, DATEDIFF(day, 0, DATEADD(hh, @diff, @UTCDateTime)),0);
END

You could then do something like:

SELECT dbo.LocalDateFromUTCTime(MyUTCDate), COUNT(*)
FROM MyTable
GROUP BY dbo.LocalDateFromUTCTime(MyUTCDate);

This does, of course, make the statement non-SARGable.

If sargability is of primary concern due to the large number of records you may have, you could create a materialized view with an index on the calculated field.


EDIT:

Per @RichardThekiwi the SARGability of this particular statement is not affected by use of the function since the function is not part of either a JOIN or a WHERE clause. Where the function is used, it is ran after any indexes would have been used.

ALSO, not the above code will truncate any time portion of the date input to the function (this is by design). Therefore, any time zones that implement minutes such as the Prince Edward Island in Canada (UTC -4:30), India (UTC -4:30), and Kathmandu (UTC +5:45)

share|improve this answer
3  
The GROUP BY and SELECT here are performed post-search, so SARGability should not really be of concern, IMHO. BTW, did you know that there are 1/2 hr steps to timezones (quite regular) and Kathmandu uses UTC/GMT +5:45? –  孔夫子 Oct 19 '12 at 22:37
    
Thanks for that clarification - you're right the function would only affect sargability if it was part of a WHERE clause or used by a JOIN. And, no, I had not thought about time zones with minute resolution. –  Max Vernon Oct 20 '12 at 4:11
add comment

Normally, I use this expression

CAST(LEFT(GetDate() - GetUtcDate() + MyUtcDate, 11) AS DATETIME)

But on SQL Server 2008, that simplifies to

CAST(GetDate() - GetUtcDate() + MyUtcDate AS DATE)

Other than giving alternative expressions here, if you're a T-SQL purist married to DATE functions to manipulate datetimes, I don't think there's a more concise way to get the local time beyond using a scalar function (as Max has shown or CLR function).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.