# script: scripts/06_session_override.sql | database: lab-cst | started (UTC): 2026-10-16 01:52:44 (re-run)
/* 06 - Session-level override: SET TIME ZONE (run on BOTH databases)
   Goal: precedence (session > database > platform), SET TIME ZONE LOCAL reverting to the DATABASE
   setting, persistence across batches, transaction behaviour, error cases. */
SET NOCOUNT ON;
PRINT '=== 06 SET TIME ZONE on ' + DB_NAME();
=== 06 SET TIME ZONE on lab-cst
SELECT '06.0 start (inherits DB setting)' AS step, CURRENT_TIMEZONE_ID() AS tz_id, SYSDATETIMEOFFSET() AS now_offset,
       (SELECT time_zone FROM sys.dm_exec_sessions WHERE session_id = @@SPID) AS dm_exec_sessions_time_zone;
step|tz_id|now_offset|dm_exec_sessions_time_zone
----|-----|----------|--------------------------
06.0 start (inherits DB setting)|Central Standard Time|2026-10-15 20:52:45.5699071 -05:00|LOCAL

PRINT '--- 06.1 SET TIME ZONE ''India Standard Time'' (+05:30)';
SET TIME ZONE 'India Standard Time';
SELECT '06.1 same batch' AS step, CURRENT_TIMEZONE_ID() AS tz_id, SYSDATETIMEOFFSET() AS now_offset, GETDATE() AS getdate_, GETUTCDATE() AS getutcdate_,
       (SELECT time_zone FROM sys.dm_exec_sessions WHERE session_id = @@SPID) AS dm_exec_sessions_time_zone;
--- 06.1 SET TIME ZONE 'India Standard Time' (+05:30)
step|tz_id|now_offset|getdate_|getutcdate_|dm_exec_sessions_time_zone
----|-----|----------|--------|-----------|--------------------------
06.1 same batch|India Standard Time|2026-10-16 07:22:45.6028034 +05:30|2026-10-16 07:22:45.600|2026-10-16 01:52:45.600|LOCAL

SELECT '06.2 next batch (does it persist?)' AS step, CURRENT_TIMEZONE_ID() AS tz_id, SYSDATETIMEOFFSET() AS now_offset;
step|tz_id|now_offset
----|-----|----------
06.2 next batch (does it persist?)|India Standard Time|2026-10-16 07:22:45.6381288 +05:30

PRINT '--- 06.3 45-minute offset zone';
SET TIME ZONE 'Nepal Standard Time';
SELECT '06.3 Nepal' AS step, CURRENT_TIMEZONE_ID() AS tz_id, SYSDATETIMEOFFSET() AS now_offset;
--- 06.3 45-minute offset zone
step|tz_id|now_offset
----|-----|----------
06.3 Nepal|Nepal Standard Time|2026-10-16 07:37:45.6741639 +05:45

PRINT '--- 06.4 SET TIME ZONE LOCAL -> back to the DATABASE setting (not necessarily UTC)';
SET TIME ZONE LOCAL;
SELECT '06.4 after LOCAL' AS step, CURRENT_TIMEZONE_ID() AS tz_id, SYSDATETIMEOFFSET() AS now_offset,
       (SELECT time_zone FROM sys.dm_exec_sessions WHERE session_id = @@SPID) AS dm_exec_sessions_time_zone;
--- 06.4 SET TIME ZONE LOCAL -> back to the DATABASE setting (not necessarily UTC)
step|tz_id|now_offset|dm_exec_sessions_time_zone
----|-----|----------|--------------------------
06.4 after LOCAL|Central Standard Time|2026-10-15 20:52:45.7081556 -05:00|Nepal Standard Time

PRINT '--- 06.5 SET TIME ZONE inside a transaction that is rolled back';
BEGIN TRAN;
SET TIME ZONE 'Tokyo Standard Time';
ROLLBACK;
SELECT '06.5 after ROLLBACK' AS step, CURRENT_TIMEZONE_ID() AS tz_id;
SET TIME ZONE LOCAL;
--- 06.5 SET TIME ZONE inside a transaction that is rolled back
step|tz_id
----|-----
06.5 after ROLLBACK|Tokyo Standard Time

PRINT '--- 06.6 Variable as the zone name';
DECLARE @tz sysname = N'Tokyo Standard Time';
SET TIME ZONE @tz;
SELECT '06.6 variable' AS step, CURRENT_TIMEZONE_ID() AS tz_id;
SET TIME ZONE LOCAL;
Msg 102, Level 15, State 1, Server your-server, Line 3
Incorrect syntax near '@tz'.
PRINT '--- 06.7 EXPECT ERROR: abbreviation ''IST''';
SET TIME ZONE 'IST';
Msg 12149, Level 16, State 2, Server your-server, Line 2
Provided time zone ID 'IST' is not available in this database.
PRINT '--- 06.8 EXPECT ERROR: IANA ''Asia/Kolkata''';
SET TIME ZONE 'Asia/Kolkata';
Msg 12149, Level 16, State 2, Server your-server, Line 2
Provided time zone ID 'Asia/Kolkata' is not available in this database.
PRINT '--- 06.9 ANSI interval form SET TIME ZONE INTERVAL ''+05:30'' HOUR TO MINUTE';
SET TIME ZONE INTERVAL '+05:30' HOUR TO MINUTE;
SELECT '06.9 interval' AS step, CURRENT_TIMEZONE_ID() AS tz_id, SYSDATETIMEOFFSET() AS now_offset;
Msg 102, Level 15, State 1, Server your-server, Line 2
Incorrect syntax near 'INTERVAL'.
PRINT '--- 06.10 Offset string ''+05:30''';
SET TIME ZONE '+05:30';
SELECT '06.10 offset string' AS step, CURRENT_TIMEZONE_ID() AS tz_id, SYSDATETIMEOFFSET() AS now_offset;
Msg 12149, Level 16, State 2, Server your-server, Line 2
Provided time zone ID '+05:30' is not available in this database.
PRINT '--- 06.11 Error after a failed SET: what is the effective zone now?';
SELECT '06.11 state after errors' AS step, CURRENT_TIMEZONE_ID() AS tz_id;
SET TIME ZONE LOCAL;
SELECT '06.11 after LOCAL' AS step, CURRENT_TIMEZONE_ID() AS tz_id;
--- 06.11 Error after a failed SET: what is the effective zone now?
step|tz_id
----|-----
06.11 state after errors|Central Standard Time

step|tz_id
----|-----
06.11 after LOCAL|Central Standard Time

PRINT '--- 06.13 Does sys.dm_exec_sessions.time_zone lag behind SET TIME ZONE? (SET in one batch, read in the next ones)';
SET TIME ZONE 'Tokyo Standard Time';
SELECT '06.13a same batch as SET Tokyo' AS step, CURRENT_TIMEZONE_ID() AS tz_id,
       (SELECT time_zone FROM sys.dm_exec_sessions WHERE session_id = @@SPID) AS dm_exec_sessions_time_zone;
--- 06.13 Does sys.dm_exec_sessions.time_zone lag behind SET TIME ZONE? (SET in one batch, read in the next ones)
step|tz_id|dm_exec_sessions_time_zone
----|-----|--------------------------
06.13a same batch as SET Tokyo|Tokyo Standard Time|LOCAL

SELECT '06.13b next batch' AS step, CURRENT_TIMEZONE_ID() AS tz_id,
       (SELECT time_zone FROM sys.dm_exec_sessions WHERE session_id = @@SPID) AS dm_exec_sessions_time_zone;
step|tz_id|dm_exec_sessions_time_zone
----|-----|--------------------------
06.13b next batch|Tokyo Standard Time|Tokyo Standard Time

SET TIME ZONE LOCAL;
SELECT '06.13c batch after SET LOCAL' AS step, CURRENT_TIMEZONE_ID() AS tz_id,
       (SELECT time_zone FROM sys.dm_exec_sessions WHERE session_id = @@SPID) AS dm_exec_sessions_time_zone;
step|tz_id|dm_exec_sessions_time_zone
----|-----|--------------------------
06.13c batch after SET LOCAL|Central Standard Time|LOCAL

SELECT '06.13d one more batch' AS step, CURRENT_TIMEZONE_ID() AS tz_id,
       (SELECT time_zone FROM sys.dm_exec_sessions WHERE session_id = @@SPID) AS dm_exec_sessions_time_zone;
step|tz_id|dm_exec_sessions_time_zone
----|-----|--------------------------
06.13d one more batch|Central Standard Time|LOCAL

PRINT '--- 06.12 Leave the session in India Standard Time; the runner then opens a NEW connection to prove isolation';
SET TIME ZONE 'India Standard Time';
SELECT '06.12 end of script' AS step, CURRENT_TIMEZONE_ID() AS tz_id;
--- 06.12 Leave the session in India Standard Time; the runner then opens a NEW connection to prove isolation
step|tz_id
----|-----
06.12 end of script|India Standard Time

# sqlcmd exit code: 0