Convert Datetime column from UTC to local time in select statement

by

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

More Related Contents:

  1. How to calculate age (in years) based on Date of Birth and getDate()
  2. CROSS JOIN vs INNER JOIN in SQL
  3. SQL Server 2012 column identity increment jumping from 6 to 1000+ on 7th entry [duplicate]
  4. INNER JOIN vs LEFT JOIN performance in SQL Server
  5. T-SQL dynamic pivot
  6. Avoid duplicates in INSERT INTO SELECT query in SQL Server
  7. Select group of rows that match all items in a list
  8. Is there any difference between IS NULL and =NULL
  9. SQL Server check case-sensitivity?
  10. SQL statement to select all rows from previous day
  11. SQL Server: Get table primary key using sql query [duplicate]
  12. How to determine the number of days in a month in SQL Server?
  13. Concatenate values based on ID
  14. How can I combine multiple rows into a comma-delimited list in SQL Server 2005?
  15. Why and when a LEFT JOIN with condition in WHERE clause is not equivalent to the same LEFT JOIN in ON? [duplicate]
  16. how to declare global variable in SQL Server..?
  17. SQL Select Upcoming Birthdays
  18. SQL : in clause in stored procedure:how to pass values
  19. Split values over multiple rows [duplicate]
  20. Best way to do nested case statement logic in SQL Server
  21. Must declare the scalar variable
  22. How to get the identity of an inserted row?
  23. Insert Update stored proc on SQL Server
  24. TSQL – How to use GO inside of a BEGIN .. END block?
  25. Finding a Top Level Parent in SQL
  26. SQL – How do I get only the numbers after the decimal?
  27. How to retrieve records for last 30 minutes in MS SQL?
  28. Connecting to Oracle Database using Sql Server Integration Services
  29. Why do SQL Server Scalar-valued functions get slower?
  30. Executing SQL query on multiple databases

Leave a Comment