Narcis Miclaus

8 min read

SQL Server, from the ground up Lesson 15 / 40

Date and time: the timezone swamp

DATETIMEOFFSET, AT TIME ZONE, DATEDIFF gotchas, storing UTC vs local, and the EU-specific horror of daylight saving. Why Runehold tags every timestamp.

If there’s one area where a production database can silently drift wrong for years before anyone notices, it’s dates and times. Runehold ships across 27 EU countries, each with its own timezone. Daylight saving changes twice a year. Customers in Italy place orders at 23:59 local; the warehouse in Poland sees them at 23:59 their local time (which might be the same or different depending on the month); finance reports them in Amsterdam time; the Shopify integration sends them in UTC. If you don’t think about this upfront, you end up with reports where “yesterday” is off by one day for 30% of your data.

This lesson is the EU-reality version of how to handle time.

The golden rule: store UTC, display local

Pick one: every timestamp in your database is stored in UTC. Every user-facing report converts to the user’s local timezone at display time. No exceptions.

Why: UTC doesn’t have daylight saving, doesn’t jump around, and is unambiguous. Any local time is a function of UTC + a timezone rule. Going UTC → local is always well-defined. Going local → local or local → UTC without knowing the source timezone is not well-defined. Store the unambiguous one.

Exception: if a timestamp represents an intent anchored to a place (“the delivery should arrive at 10:00 Amsterdam time, regardless of whether that’s CEST or CET on the day”) — you need to store both the datetime AND the timezone, which is what DATETIMEOFFSET is for. We’ll cover that.

The types again, briefly

From lesson 4:

  • DATETIME — old, 3.33ms precision, 1753–9999. Don’t use in new code.
  • DATETIME2(n) — modern, up to 100ns precision, 0001–9999. Default for UTC timestamps.
  • DATE — date only, no time. 3 bytes.
  • TIME(n) — time of day, no date. 3–5 bytes.
  • DATETIMEOFFSET(n) — datetime + offset from UTC. Use when you need to preserve the original timezone.

Rule of thumb: if the column represents “when something happened” and the app is the source of truth, use DATETIME2(0) and store UTC. If the column needs to preserve the original local timezone, use DATETIMEOFFSET.

SYSUTCDATETIME vs GETDATE vs GETUTCDATE

SELECT
    GETDATE()         AS server_local_now,    -- DATETIME, server's local timezone
    SYSDATETIME()     AS server_local_now_2,  -- DATETIME2, server's local timezone
    GETUTCDATE()      AS utc_now,              -- DATETIME, UTC
    SYSUTCDATETIME()  AS utc_now_2,            -- DATETIME2, UTC
    SYSDATETIMEOFFSET() AS utc_with_offset;    -- DATETIMEOFFSET, UTC with offset

Prefer SYSUTCDATETIME() for DEFAULT values and inserts. It’s UTC, it’s precise, it’s future-proof. Every CreatedAt, UpdatedAt, OrderDate, ShippedAt in Runehold uses it:

CREATE TABLE Sales.Orders (
    OrderId   BIGINT IDENTITY(1,1) PRIMARY KEY,
    OrderDate DATETIME2(0) NOT NULL DEFAULT SYSUTCDATETIME(),
    ...
);

Never GETDATE() in a default — if your server’s timezone ever changes (migration, server move, container restart), every row defaults to a different timezone. UTC is stable.

AT TIME ZONE: the only built-in you need

AT TIME ZONE converts a datetime to (or from) a named timezone. Added in SQL Server 2016. Hugely underused.

-- Convert UTC to Italian local time
SELECT SYSUTCDATETIME() AT TIME ZONE 'UTC' AT TIME ZONE 'W. Europe Standard Time' AS it_local;

-- Convert stored UTC back to local for a user in Amsterdam
SELECT o.OrderId,
       o.OrderDate                                                 AS utc_date,
       o.OrderDate AT TIME ZONE 'UTC' AT TIME ZONE 'W. Europe Standard Time' AS amsterdam_local
FROM Sales.Orders AS o;

Two AT TIME ZONE calls because the first one assigns a timezone to a “naive” datetime, and the second one converts to a target. A DATETIMEOFFSET column already has a timezone, so you only need one.

-- If OrderDateOffset is DATETIMEOFFSET
SELECT o.OrderDateOffset AT TIME ZONE 'W. Europe Standard Time' AS amsterdam_local
FROM Sales.Orders AS o;

Timezone names are Windows’ timezone IDs (e.g., 'W. Europe Standard Time', 'Central European Standard Time', 'GMT Standard Time'). See sys.time_zone_info:

SELECT * FROM sys.time_zone_info ORDER BY current_utc_offset;

All 140-ish IDs with their current UTC offsets. Write them down once in a lookup table.

Daylight saving: handled, but be aware

AT TIME ZONE knows about daylight saving. Convert a UTC datetime from January and one from July to Amsterdam time and you get CET (UTC+1) and CEST (UTC+2) respectively. It just works.

The gotcha: don’t compute local-time ranges by hand with + 1 or + 2. That works for half the year and is wrong for the other half. Always use AT TIME ZONE.

DATEADD and DATEDIFF

Two workhorses:

-- Add / subtract time
SELECT DATEADD(DAY,    -7,  GETDATE()) AS one_week_ago;
SELECT DATEADD(MONTH,  -1,  GETDATE()) AS one_month_ago;
SELECT DATEADD(HOUR,   -24, GETDATE()) AS yesterday_same_time;

-- Difference between two dates
SELECT DATEDIFF(DAY,   '2026-01-01', '2026-04-17') AS days;        -- 106
SELECT DATEDIFF(HOUR,  '2026-01-01 00:00', '2026-01-01 15:30') AS hours;  -- 15
SELECT DATEDIFF(MONTH, '2026-01-31', '2026-02-01') AS months;       -- 1 (!!)

DATEDIFF counts boundary crossings, not elapsed time. DATEDIFF(MONTH, '2026-01-31', '2026-02-01') returns 1 because one month boundary (Feb 1) crossed, even though only 1 day passed. Same trap with YEAR: DATEDIFF(YEAR, '2025-12-31', '2026-01-01') is 1 year.

For “age in complete months,” the safer idiom:

-- Completed months between two dates
SELECT DATEDIFF(MONTH, start_date, end_date)
     - CASE WHEN DATEPART(DAY, end_date) < DATEPART(DAY, start_date) THEN 1 ELSE 0 END
FROM ...;

For “age in years” (for GDPR consent or loyalty tier):

SELECT DATEDIFF(YEAR, BirthDate, GETDATE())
     - CASE WHEN (DATEPART(MONTH, GETDATE()) < DATEPART(MONTH, BirthDate))
            OR (DATEPART(MONTH, GETDATE()) = DATEPART(MONTH, BirthDate)
                AND DATEPART(DAY, GETDATE()) < DATEPART(DAY, BirthDate))
            THEN 1 ELSE 0 END AS age;

Or easier: compute once, store in a computed column.

Truncation: date-only, week, month

To truncate a datetime to a date:

-- Just the date, no time
SELECT CAST(OrderDate AS DATE) FROM Sales.Orders;
SELECT CONVERT(DATE, OrderDate) FROM Sales.Orders;   -- same thing, CONVERT style

To the start of the month:

SELECT DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1) AS month_start
FROM Sales.Orders;

-- Or the shorter, index-friendly way (SQL 2022+):
SELECT DATETRUNC(MONTH, OrderDate) FROM Sales.Orders;

DATETRUNC (SQL 2022+) is the cleanest. It takes a unit (DAY, WEEK, MONTH, QUARTER, YEAR) and truncates down to it.

The SARGability trap, again

Truncation functions on the indexed column break the index:

-- Bad: can't seek the index on OrderDate
WHERE CAST(OrderDate AS DATE) = '2026-03-15'

-- Good: range query, seekable
WHERE OrderDate >= '2026-03-15' AND OrderDate < '2026-03-16'

For monthly buckets:

-- Bad
WHERE YEAR(OrderDate) = 2026 AND MONTH(OrderDate) = 3

-- Good
WHERE OrderDate >= '2026-03-01' AND OrderDate < '2026-04-01'

Half-open intervals (inclusive start, exclusive end) are the right way to express “in March 2026.” Leap seconds, microseconds, midnight — none of them misbehave. Every other form has edge cases.

Formatting

FORMAT is the nice one but slow. CONVERT with style codes is the fast one but cryptic.

-- FORMAT: readable, slow
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss')   AS iso_like;
SELECT FORMAT(GETDATE(), 'dd/MM/yyyy')            AS italian;
SELECT FORMAT(GETDATE(), 'dddd, d MMMM yyyy', 'it-IT') AS italian_long;

-- CONVERT: fast, cryptic
SELECT CONVERT(VARCHAR(20), GETDATE(), 121)       AS iso_like;     -- 2026-04-17 10:30:45.000
SELECT CONVERT(VARCHAR(10), GETDATE(), 103)       AS euro_short;   -- 17/04/2026

Cheat sheet for the style codes that matter:

  • 101 — US MM/DD/YYYY
  • 103 — European DD/MM/YYYY
  • 105 — Italian DD-MM-YYYY
  • 120 — ODBC YYYY-MM-DD HH:MM:SS
  • 121 — ODBC with ms YYYY-MM-DD HH:MM:SS.mmm
  • 126 — ISO 8601 YYYY-MM-DDTHH:MM:SS.mmm

Never format dates in the database for API responses. Return the raw datetime (or ISO-8601 string) to the app and format there. Formatting in SQL is for human-facing reports only, and even then it often belongs in the reporting tool (Power BI, Grafana) rather than the query.

ISO 8601 is your friend

For any date passed as a string literal in T-SQL, use ISO 8601:

-- Good: unambiguous everywhere
WHERE OrderDate >= '2026-03-15';
WHERE OrderDate >= '2026-03-15T00:00:00';
WHERE OrderDate >= '20260315';              -- also ISO, no separator

-- Ambiguous: interpretation depends on language setting
WHERE OrderDate >= '03/15/2026';            -- month-first? day-first?
WHERE OrderDate >= '15/03/2026';

ISO 8601 is culture-invariant. '2026-03-15' means March 15th of 2026 in every language SQL Server knows about. That’s it. Set it and forget it.

EU-specific: date formats across languages

Run this on your server to see how SET LANGUAGE affects date parsing:

-- English (US)
SET LANGUAGE us_english;
SELECT CAST('03/15/2026' AS DATETIME);     -- March 15

-- Italian
SET LANGUAGE Italian;
SELECT CAST('15/03/2026' AS DATETIME);     -- March 15

-- Both
SET LANGUAGE British;
SELECT CAST('15/03/2026' AS DATETIME);     -- March 15

Swapping language changes the default. That’s why ISO 8601 saves you: language-independent. Don’t rely on SET LANGUAGE to match your users’ expectations; convert explicitly.

Run this on your own machine

USE Runehold;
GO

-- 1. Current time in various forms
SELECT
    GETDATE()           AS local_now,
    SYSDATETIME()       AS local_now_precise,
    GETUTCDATE()        AS utc_now,
    SYSUTCDATETIME()    AS utc_now_precise,
    SYSDATETIMEOFFSET() AS utc_with_offset;

-- 2. Convert UTC stored dates to local time
SELECT o.OrderId,
       o.OrderDate AS utc_date,
       o.OrderDate AT TIME ZONE 'UTC'
         AT TIME ZONE 'W. Europe Standard Time' AS amsterdam_local,
       o.OrderDate AT TIME ZONE 'UTC'
         AT TIME ZONE 'Central European Standard Time' AS berlin_local
FROM Sales.Orders AS o;

-- 3. DATEDIFF boundary quirks
SELECT DATEDIFF(MONTH, '2026-01-31', '2026-02-01') AS misleading_months,  -- 1
       DATEDIFF(DAY,   '2026-01-31', '2026-02-01') AS real_days;          -- 1

-- 4. Monthly revenue using DATETRUNC (2022+) vs manual
SELECT DATETRUNC(MONTH, OrderDate) AS month_start,
       SUM(Total) AS revenue
FROM Sales.Orders
GROUP BY DATETRUNC(MONTH, OrderDate)
ORDER BY month_start;

-- 5. SARGable vs non-SARGable (visible in execution plan)
-- Non-SARGable:
SELECT * FROM Sales.Orders WHERE YEAR(OrderDate) = 2026;

-- SARGable:
SELECT * FROM Sales.Orders
WHERE OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01';

-- 6. Generate a date dimension on the fly
WITH dates AS (
    SELECT CAST('2026-01-01' AS DATE) AS d
    UNION ALL
    SELECT DATEADD(DAY, 1, d) FROM dates WHERE d < '2026-01-31'
)
SELECT d,
       DATENAME(WEEKDAY, d) AS day_name,
       DATEPART(ISO_WEEK, d) AS iso_week
FROM dates
OPTION (MAXRECURSION 500);

Run each. Notice in query 2 how Amsterdam and Berlin local times are identical (they share a zone) but different from UTC — that’s AT TIME ZONE handling daylight saving correctly.

Next lesson: recursive CTEs for real. Org charts, folder trees, bill of materials, generating date ranges without a date dimension. Building on what we already saw in lesson 11 but going deep.

Search