How to Convert a UTC Datetime Column to Local Time in SQL SELECT Statements?

Convert UTC date and time column to local time in SQL SELECT statement

When executing SQL SELECT queries, it may be necessary to convert UTC datetime columns to local time to enhance usability and improve the accuracy of data representation. This can be achieved through techniques specific to the SQL version used.

For SQL Server 2008 or higher:

• Use the CONVERT function combined with SWITCHOFFSET and CONVERT(datetimeoffset) to convert UTC columns to local time, taking into account the system time zone offset.

SQL syntax:

<code class="language-sql">SELECT CONVERT(datetime, 
               SWITCHOFFSET(CONVERT(datetimeoffset, 
                                    MyTable.UtcColumn), 
                            DATENAME(TzOffset, SYSDATETIMEOFFSET()))) 
       AS ColumnInLocalTime
FROM MyTable</code>

• Alternatively, use the more concise DATEADD function for conversion.

SQL syntax:

<code class="language-sql">SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), MyTable.UtcColumn) 
       AS ColumnInLocalTime
FROM MyTable</code>

Note:

Avoid using the minus sign - to subtract dates for conversion, as this may lead to unpredictable results due to race conditions between system and local datetimes.

Additional notes:

The provided method does not take daylight saving time (DST) into account. For DST adjustments, please refer to the Stack Overflow topic:

"How to create daylight saving time start and end functions in SQL Server"

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