How to Convert UTC Datetime Columns to Local Time in SQL Select Statements?

Converting UTC Datetime Columns to Local Time in SQL Select Statements

When executing SQL select queries, it may be necessary to convert UTC datetime columns into local time to facilitate easier interpretation and display. This conversion can be accomplished without modifying the database or using external code.

Conversion Method

In SQL Server 2008 or later, the following syntax can be used:

SELECT CONVERT(datetime, 
               SWITCHOFFSET(CONVERT(datetimeoffset, 
                                    MyTable.UtcColumn), 
                            DATENAME(TzOffset, SYSDATETIMEOFFSET()))) 
       AS ColumnInLocalTime
FROM MyTable

A shorter alternative is:

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

Considerations

It's important to avoid using subtraction (-) to calculate date differences, as this approach can lead to inaccurate results due to non-atomic operations.

DST Adjustment

The provided methods do not account for Daylight Saving Time (DST). If DST adjustment is required, refer to the following question:

• [How to create Daylight Savings time Start and End function in SQL Server](https://stackoverflow.com/questions/2614192/how-to-create-daylight-savings-time-start-and-end-function-in-sql-server)

The above is the detailed content of How to Convert UTC Datetime Columns to Local Time in SQL Select Statements?. For more information, please follow other related articles on the PHP Chinese website!