Sample Header Ad - 728x90

Is there a more concise way to convert a UTC datetime into a local date only?

7 votes
3 answers
22871 views
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)
Asked by Rachel (8557 rep)
Oct 19, 2012, 07:37 PM
Last activity: Oct 13, 2022, 06:36 PM