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
You can do this as follows on SQL Server 2008 or greater:
SELECT CONVERT(datetime,
SWITCHOFFSET(CONVERT(datetimeoffset,
MyTable.UtcColumn),
DATENAME(TzOffset, SYSDATETIMEOFFSET())))
AS ColumnInLocalTime
FROM MyTable
You can also do the less verbose:
SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), MyTable.UtcColumn)
AS ColumnInLocalTime
FROM MyTable
Whatever you do, do not use -
to subtract dates, because the operation
is not atomic, and you will on occasion get indeterminate results due to race
conditions between the system datetime and the local datetime being checked at
different times (i.e., non-atomically).
Please note that this answer does not take DST into account. If you want to include a DST adjustment, please also see the following SO question:
[How to create Daylight Savings time Start and End function in SQL Server](https://stackoverflow.com/questions/19732896/how-to-create-daylight- savings-time-start-and-end-function-in-sql-server)