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

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)

This answer does not take into account Daylight Savings Time changes, the addition of leap seconds, and is insensitive to time zones that are not whole-hour offsets from UTC. See my 2nd answer on this question for a far more accurate way of doing this for SQL Server 2016 and above.

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);

The above code will truncate any time portion of the date input to the function (this is by design). Therefore, as noted at the start of my answer, 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), will not see correct results.

Be aware using a function like this on a large number of rows will be terrible for performance. The AT TIME ZONE construct as shown below does not suffer as much from that problem, although it is by no means a silver bullet, performance-wise.

StackExchange Database Administrators Q#27276, answer score: 6