Convert Datetime column from UTC to local time in select statement

Question

I'm doing a few SQL select queries and would like to convert my UTC datetime column into local time to be displayed as local time in my query results. Note, I am NOT looking to do this conversion via code but rather when I am doing manual and random SQL queries against my databases.

Answer 1

For SQL Server 2008 or greater you can use below code:

SELECT CONVERT(datetime, 

               SWITCHOFFSET(CONVERT(datetimeoffset, 

                                    MyTable.UtcColumn), 

                            DATENAME(TzOffset, SYSDATETIMEOFFSET()))) 

       AS ColumnInLocalTime

FROM MyTable

Or else you can use less verbose like this:

SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), MyTable.UtcColumn) 

       AS ColumnInLocalTime

FROM MyTable

Note: In any case, you should not use - to subtract dates, because the operation is not atomic, sometimes you can get indeterminate results because of the race conditions between the system datetime and 

the local datetime which will be checked at different times (i.e., non-atomically).