How to Convert UTC Datetime to Local Time in SQL Server?

Convert UTC datetime column to local time in SQL query

Handling UTC datetimes in SQL queries can sometimes be inconvenient, especially when you need to display them in your local time zone. There are simple techniques to convert UTC datetime to local time, eliminating the need for code-based conversions.

Solution for SQL Server 2008 and above:

For SQL Server 2008 or higher, use the following query:

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

Alternative syntax:

Alternatively, you can use the following simplified version:

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

Note: Avoid subtracting dates

It is important to note that using the - operator to subtract dates is not recommended as this may lead to indeterminate results due to race conditions.

Consider Daylight Saving Time (DST)

This provided solution does not take DST adjustments into account. If necessary, please refer to the following Stack Overflow question:

//m.sbmmt.com/link/95743904f0de5aa6327dee27efdc81b4

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