Skip to main content

Dates and Times in SQL Server: AT TIME ZONE

This article contains steps how to calculate dates and times in different regions in SQL

In SQL Server, you can work with dates, times, and time zones using data types and functions such as DATETIMEOFFSET, GETUTCDATE(), and others. Here’s a guide on how to set time zones, store data with time zones, and query them:

Dynamic time zone handling (SQL Server 2016+)

SQL Server 2016 introduced support for time zones via the AT TIME ZONE library.
Example: Converting to another time zone:

SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'Central European Standard Time' AS CentralEuropeanTime;

Attaching a time zone to a stored date

If a DATETIME or DATETIME2 value does not already have time zone information, you can attach a time zone using AT TIME ZONE.

SELECT CAST('2024-11-28 12:00:00' AS DATETIME2) AT TIME ZONE 'UTC' AS TimeWithTimeZone;

Converting between time zones:

SELECT EventTime AT TIME ZONE 'UTC' AS EventTimeUTC,
       EventTime AT TIME ZONE 'Central European Standard Time' AS EventTimeCET
FROM Events;

Handling daylight saving time

The AT TIME ZONE function automatically adjusts for daylight saving time (DST) based on the specified time zone.

SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'Pacific Standard Time' AS PSTTime,
       SYSDATETIMEOFFSET() AT TIME ZONE 'Central European Standard Time' AS CETTime;
  • For Pacific Standard Time, it will handle the switch between standard time and daylight saving time.
  • For Central European Standard Time, it adjusts based on European DST rules.

List of available time zones

SQL Server provides a system view, sys.time_zone_info, that lists all available time zones.

SELECT * FROM sys.time_zone_info;

This way, you can efficiently handle time zones in SQL Server, store data with offsets, and convert them as needed.

Back to top