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)
The best way to convert a non-current UTC date into local time, prior to SQL Server 2016, is to use the Microsoft .Net Common Language Runtime, or CLR.
The code itself is easy; the difficult part is usually convincing people that the CLR isn't pure evil or scary...
For one of the many examples, check out Harsh Chawla's blog post on the topic.
Unfortunately, there is nothing built-in prior to SQL Server 2016 that can handle this type of conversion, save for CLR-based solutions. You could write a T-SQL function which does something like this, but then you'd have to implement the date-change logic yourself, and I'd call that decidedly not easy.
Best Answer
To get local time, your best bet is to use the CLR, if that is available to you. Matt Whitfield and Slawomir Brys have sample code you could use to convert which can handle Daylight Savings Time.
Regarding why the DMV is in UTC rather than local time, Service Broker can work across instances, so having service broker-related functionality all in UTC makes sense--that way, there isn't a problem with date synchronization between your east coast server and Tokyo branch. That'd be my best guess as to why that particular DMV is in UTC whereas a number of others (e.g., dm_exec_sessions) are in local time.