# script: scripts/08_at_time_zone_and_dst.sql | database: lab-cst | started (UTC): 2026-10-16 01:49:56 /* 08 - AT TIME ZONE is not affected by the setting + Daylight Saving Time traps (CST database) US Central 2026: DST starts Sun 2026-03-08 02:00 -> 03:00 (gap), ends Sun 2026-11-01 02:00 -> 01:00 (overlap). */ SET NOCOUNT ON; PRINT '=== 08 AT TIME ZONE and DST on ' + DB_NAME() + ' (effective tz: ' + CURRENT_TIMEZONE_ID() + ')'; === 08 AT TIME ZONE and DST on lab-cst (effective tz: Central Standard Time) PRINT '--- 08.1 AT TIME ZONE gives the same result under DB=CST and under session=India'; DECLARE @v datetime2(0) = '2026-07-01 12:00:00'; SELECT 'DB setting' AS ctx, CURRENT_TIMEZONE_ID() AS tz_id, @v AT TIME ZONE 'UTC' AS as_utc, @v AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time' AS utc_to_central; SET TIME ZONE 'India Standard Time'; SELECT 'session India' AS ctx, CURRENT_TIMEZONE_ID() AS tz_id, @v AT TIME ZONE 'UTC' AS as_utc, @v AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time' AS utc_to_central; SET TIME ZONE LOCAL; --- 08.1 AT TIME ZONE gives the same result under DB=CST and under session=India ctx|tz_id|as_utc|utc_to_central ---|-----|------|-------------- DB setting|Central Standard Time|2026-07-01 12:00:00 +00:00|2026-07-01 07:00:00 -05:00 ctx|tz_id|as_utc|utc_to_central ---|-----|------|-------------- session India|India Standard Time|2026-07-01 12:00:00 +00:00|2026-07-01 07:00:00 -05:00 PRINT '--- 08.2 AT TIME ZONE with CURRENT_TIMEZONE_ID() as the target (display UTC data in the session zone)'; SELECT CAST('2026-07-01 12:00' AS datetime2(0)) AT TIME ZONE 'UTC' AT TIME ZONE CURRENT_TIMEZONE_ID() AS utc_noon_in_current_zone; --- 08.2 AT TIME ZONE with CURRENT_TIMEZONE_ID() as the target (display UTC data in the session zone) utc_noon_in_current_zone ------------------------ 2026-07-01 07:00:00 -05:00 PRINT '--- 08.3 Naming trap: zone id is "Central Standard Time" but today the offset is daylight (-05:00)'; SELECT CURRENT_TIMEZONE_ID() AS tz_id, CURRENT_TIMEZONE() AS tz_display, DATEPART(TZOFFSET, SYSDATETIMEOFFSET()) AS offset_min_now, (SELECT current_utc_offset FROM sys.time_zone_info WHERE name = 'Central Standard Time') AS tzinfo_offset, (SELECT is_currently_dst FROM sys.time_zone_info WHERE name = 'Central Standard Time') AS is_currently_dst; --- 08.3 Naming trap: zone id is "Central Standard Time" but today the offset is daylight (-05:00) tz_id|tz_display|offset_min_now|tzinfo_offset|is_currently_dst -----|----------|--------------|-------------|---------------- Central Standard Time|(UTC-06:00) Central Time (US & Canada)|-300|-05:00|1 PRINT '--- 08.4 Spring-forward GAP: local 02:30 on 2026-03-08 does not exist'; SELECT CAST('2026-03-08 01:59:00' AS datetime2(0)) AT TIME ZONE 'Central Standard Time' AS before_gap, CAST('2026-03-08 02:30:00' AS datetime2(0)) AT TIME ZONE 'Central Standard Time' AS inside_gap_nonexistent, CAST('2026-03-08 03:00:00' AS datetime2(0)) AT TIME ZONE 'Central Standard Time' AS after_gap; PRINT ' naive DATEDIFF says 61 minutes between 01:59 and 03:00 local; real elapsed time:'; SELECT DATEDIFF(MINUTE, CAST('2026-03-08 01:59' AS datetime2), CAST('2026-03-08 03:00' AS datetime2)) AS naive_minutes, DATEDIFF(MINUTE, CAST('2026-03-08 01:59' AS datetime2) AT TIME ZONE 'Central Standard Time', CAST('2026-03-08 03:00' AS datetime2) AT TIME ZONE 'Central Standard Time') AS real_minutes; --- 08.4 Spring-forward GAP: local 02:30 on 2026-03-08 does not exist before_gap|inside_gap_nonexistent|after_gap ----------|----------------------|--------- 2026-03-08 01:59:00 -06:00|2026-03-08 03:30:00 -05:00|2026-03-08 03:00:00 -05:00 naive DATEDIFF says 61 minutes between 01:59 and 03:00 local; real elapsed time: naive_minutes|real_minutes -------------|------------ 61|1 PRINT '--- 08.5 Fall-back OVERLAP: two events 1 hour apart in UTC get the SAME naive local timestamp'; DECLARE @e TABLE (event_name varchar(10), utc datetime2(0)); INSERT @e VALUES ('event A', '2026-11-01 06:30:00'), ('event B', '2026-11-01 07:30:00'); SELECT event_name, utc, CAST(utc AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time' AS datetime2(0)) AS local_naive_datetime2, utc AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time' AS local_datetimeoffset FROM @e; SELECT DATEDIFF(MINUTE, MIN(CAST(utc AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time' AS datetime2(0))), MAX(CAST(utc AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time' AS datetime2(0)))) AS naive_minutes_apart, DATEDIFF(MINUTE, MIN(utc), MAX(utc)) AS real_minutes_apart FROM @e; --- 08.5 Fall-back OVERLAP: two events 1 hour apart in UTC get the SAME naive local timestamp event_name|utc|local_naive_datetime2|local_datetimeoffset ----------|---|---------------------|-------------------- event A|2026-11-01 06:30:00|2026-11-01 01:30:00|2026-11-01 01:30:00 -05:00 event B|2026-11-01 07:30:00|2026-11-01 01:30:00|2026-11-01 01:30:00 -06:00 naive_minutes_apart|real_minutes_apart -------------------|------------------ 0|60 PRINT '--- 08.6 Ambiguous local 01:30 on 2026-11-01: which offset does AT TIME ZONE pick?'; SELECT CAST('2026-11-01 01:30:00' AS datetime2(0)) AT TIME ZONE 'Central Standard Time' AS ambiguous_0130; --- 08.6 Ambiguous local 01:30 on 2026-11-01: which offset does AT TIME ZONE pick? ambiguous_0130 -------------- 2026-11-01 01:30:00 -05:00 PRINT '--- 08.7 Length of the local "day" on DST dates (hours between local midnights)'; SELECT d AS local_date, DATEDIFF(HOUR, CAST(d AS datetime2) AT TIME ZONE 'Central Standard Time', CAST(DATEADD(DAY, 1, d) AS datetime2) AT TIME ZONE 'Central Standard Time') AS real_hours_in_day FROM (VALUES (CAST('2026-03-08' AS date)), ('2026-07-01'), ('2026-11-01')) v(d); --- 08.7 Length of the local "day" on DST dates (hours between local midnights) local_date|real_hours_in_day ----------|----------------- 2026-03-08|23 2026-07-01|24 2026-11-01|25 PRINT '--- 08.8 Static-offset trap: DATEADD(HOUR,-5) vs AT TIME ZONE for winter and summer UTC values'; SELECT utc, DATEADD(HOUR, -5, utc) AS static_minus5, CAST(utc AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time' AS datetime2(0)) AS correct_local, CASE WHEN DATEADD(HOUR, -5, utc) = CAST(utc AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time' AS datetime2(0)) THEN 'ok' ELSE 'WRONG by 1h' END AS static_offset_result FROM (VALUES (CAST('2026-01-15 18:00' AS datetime2(0))), ('2026-07-15 18:00')) v(utc); --- 08.8 Static-offset trap: DATEADD(HOUR,-5) vs AT TIME ZONE for winter and summer UTC values utc|static_minus5|correct_local|static_offset_result ---|-------------|-------------|-------------------- 2026-01-15 18:00:00|2026-01-15 13:00:00|2026-01-15 12:00:00|WRONG by 1h 2026-07-15 18:00:00|2026-07-15 13:00:00|2026-07-15 13:00:00|ok # sqlcmd exit code: 0