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
Last activity: Oct 13, 2022, 06:36 PM