Convert Datetime column from UTC to local time in select statement

sql

datetime

sql-server

timezone

byAnton Shumikhin·Dec 14, 2024

Change UTC datetime to local time in SQL with AT TIME ZONE functionality:

SELECT 
    UTC_Column AT TIME ZONE 'UTC' AT TIME ZONE 'your_time_zone' AS LocalTime 
FROM 
    YourTable;

Here, specify your_time_zone with your relevant time zone, such as 'Eastern Standard Time'. Using AT TIME ZONE twice considers the column as UTC first, then converts it into your desired local time.

DIVE}__DEEPIntoTimeZones

Switching Up Time Zones in SQL Server old-school Style

If sunk in the classic charm of SQL Server versions prior to 2016, use DATEADD and DATEDIFF:

SELECT
    -- Time travelling like Marty McFly here
    DATEADD(HOUR, DATEDIFF(HOUR, GETUTCDATE(), GETDATE()), UTC_Column) AS LocalTime
FROM
    YourTable;

This statement doesn't need a DeLorean! It calculates the hour difference between UTC and local time and adjusts your UTC column.

Dodging Daylight Saving Time Surprises

Regions with Daylight Saving Time (DST) hold a fun little twist - the need for some custom code-fu:

SELECT
    -- It's Just a Jump to the Left...
    AdjustToDST(UTC_Column) AS LocalTime
FROM
    YourTable;

AdjustToDST would be your custom function factoring in DST start and end dates.

SQL-CruiseControl_2016&AzureEdition

Simpler SQL Server and Azure

If you're hanging out in SQL Server 2016 or above, or just cruising in Azure - AT TIME ZONE has got you covered. And checkout sys.time_zone_info for all time zone names on the SQL starship.

DeepCodingHeaven_CLR

Programming Proficiency with CLR

Going for the gold? User-defined CLR procedures are like time zone ninjas, sneaking in faster and more flexible conversions.

TimeZones_Unmasked

NameGame

Be wary of naming nuances in time zones. datetimeoffset can ensure accuracy, as it carries both datetime and timezone offset in its pocket.

Seasons_Change

Remember, some time zones flex muscle in two forms – summer and winter. This is due to DST. Fear not! With the DATEPART and TZOFFSET functions, you can dynamically find the correct offset:

SELECT 
    -- Who knew SQL could also be a weatherman?
    DATEADD(MINUTE, DATEPART(TZOFFSET, SYSDATETIMEOFFSET()), UTC_Column) AS LocalTime
FROM 
    YourTable;