How to Convert Between Time Zones in SQL Server

Galaxy Glossary

How do I convert datetime values between time zones in SQL Server?

Converts datetime values from one time zone to another using AT TIME ZONE or SWITCHOFFSET.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why convert time zones in SQL Server?

Global apps display local timestamps. Converting in-query keeps logic server-side and guarantees uniform calculations across clients.

Which built-in functions handle time zones?

SQL Server 2016+ offers AT TIME ZONE for round-trip conversion. Versions 2008–2014 rely on SWITCHOFFSET combined with DATEADD.

What is the basic `AT TIME ZONE` pattern?

SELECT datetime_value AT TIME ZONE 'SourceZone' AT TIME ZONE 'TargetZone'; attaches the source zone, then converts to the target.

Convert UTC `order_date` to US Pacific

SELECT id, order_date AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time' AS pacific_timeFROM Orders;

How do I drop the offset?

Cast to datetime or datetime2: CAST(order_date AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time' AS datetime2).

Can I filter by date efficiently?

Filter in UTC: compute @utc_start / @utc_end, query, then convert results for presentation. This keeps index seeks fast.

Best practices

Store timestamps in UTC, use named Windows zones (not numeric offsets), and add a persisted computed column if you must index local time.

Common mistakes

Hard-coding offsets ignores daylight saving; always use zone names. Never apply AT TIME ZONE to values already typed as datetimeoffset.

Performance tips

Convert only selected columns, not predicates. Materialize local time in the presentation layer or a view, not in hot OLTP paths.

Can I handle multiple customer zones?

Join Orders with a Customer time-zone column and call AT TIME ZONE per row, or pre-aggregate in UTC and convert in the reporting layer.

Why How to Convert Between Time Zones in SQL Server is important

How to Convert Between Time Zones in SQL Server Example Usage


-- Show each order in customer local time using AT TIME ZONE
SELECT o.id,
       c.name,
       o.order_date                                   AS utc_time,
       o.order_date AT TIME ZONE 'UTC' AT TIME ZONE c.preferred_tz AS local_time
FROM   Orders  o
JOIN   Customers c ON c.id = o.customer_id;

How to Convert Between Time Zones in SQL Server Syntax


-- Attach source zone and convert to target
SELECT <datetime_expression>
       AT TIME ZONE '<SourceZone>'      -- e.g., 'UTC'
       AT TIME ZONE '<TargetZone>'      -- e.g., 'Pacific Standard Time'
;

-- Older versions (pre-2016)
SELECT SWITCHOFFSET(
         TODATETIMEOFFSET(<datetime_expression>, 0), -- treat as UTC
         '+08:00'                                    -- target offset
       );

Common Mistakes

Hard-coding numeric offsets like -08:00. Daylight saving changes will make results wrong. Fix by using named Windows time zones such as 'Pacific Standard Time'.
Running AT TIME ZONE on a datetimeoffset column. The value already has an offset, so the extra call double-converts it. Remove the first AT TIME ZONE or cast to datetime2 before converting.

Frequently Asked Questions (FAQs)

Does AT TIME ZONE adjust for daylight saving automatically?

Yes. When you supply a Windows time-zone name, SQL Server applies the correct offset for each date, including DST transitions.

Can I convert time zones in SQL Server 2012?

Yes, but AT TIME ZONE is unavailable. Use TODATETIMEOFFSET to mark the value as UTC, then SWITCHOFFSET to the desired offset.

Is there a performance penalty for AT TIME ZONE?

Minimal in SELECT lists. Avoid using it in WHERE clauses on large tables; filter in UTC instead, then convert in the projection.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.