# script: scripts/03_set_db_timezone.sql | database: lab-cst | started (UTC): 2026-10-16 01:49:43
/* 03 - Set the database-level time zone on the CST database
   Goal: (a) error behaviour of common mistakes, (b) the valid statement, (c) immediate effect on the
   current session, (d) FOR SECONDARY behaviour.
   Each attempt is its own batch so one failure does not stop the next test. */
SET NOCOUNT ON;
PRINT '=== 03 ALTER DATABASE SCOPED CONFIGURATION SET TIME_ZONE on ' + DB_NAME();
SELECT 'before' AS step, CURRENT_TIMEZONE_ID() AS tz_id, SYSDATETIMEOFFSET() AS now_offset;
=== 03 ALTER DATABASE SCOPED CONFIGURATION SET TIME_ZONE on lab-cst
step|tz_id|now_offset
----|-----|----------
before|UTC|2026-10-16 01:49:43.7565508 +00:00

PRINT '--- 03.1 EXPECT ERROR: abbreviation ''CST'' is not a Windows time zone id';
ALTER DATABASE SCOPED CONFIGURATION SET TIME_ZONE = 'CST';
Msg 12149, Level 16, State 1, Server your-server, Line 2
Provided time zone ID 'CST' is not available in this database.
PRINT '--- 03.2 EXPECT ERROR: IANA id ''America/Chicago''';
ALTER DATABASE SCOPED CONFIGURATION SET TIME_ZONE = 'America/Chicago';
Msg 12149, Level 16, State 1, Server your-server, Line 2
Provided time zone ID 'America/Chicago' is not available in this database.
PRINT '--- 03.3 EXPECT ERROR: friendly name ''Central Time''';
ALTER DATABASE SCOPED CONFIGURATION SET TIME_ZONE = 'Central Time';
Msg 12149, Level 16, State 1, Server your-server, Line 2
Provided time zone ID 'Central Time' is not available in this database.
PRINT '--- 03.4 EXPECT ERROR: offset string ''-06:00''';
ALTER DATABASE SCOPED CONFIGURATION SET TIME_ZONE = '-06:00';
Msg 12149, Level 16, State 1, Server your-server, Line 2
Provided time zone ID '-06:00' is not available in this database.
PRINT '--- 03.5 EXPECT ERROR: unquoted value (what the BACPAC/DACPAC scripting bug produces)';
ALTER DATABASE SCOPED CONFIGURATION SET TIME_ZONE = Central Standard Time;
Msg 102, Level 15, State 1, Server your-server, Line 2
Incorrect syntax near 'Central'.
PRINT '--- 03.6 EXPECT ERROR: curly quotes copied from the blog post';
ALTER DATABASE SCOPED CONFIGURATION SET TIME_ZONE = ‘Central Standard Time’;
Msg 102, Level 15, State 1, Server your-server, Line 2
Incorrect syntax near '‘'.
PRINT '--- 03.7 Variable instead of literal';
DECLARE @tz sysname = N'Central Standard Time';
ALTER DATABASE SCOPED CONFIGURATION SET TIME_ZONE = @tz;
Msg 102, Level 15, State 1, Server your-server, Line 3
Incorrect syntax near '@tz'.
PRINT '--- 03.8 Lower-case id (is the name case-insensitive? what gets stored?)';
ALTER DATABASE SCOPED CONFIGURATION SET TIME_ZONE = 'central standard time';
SELECT 'after lower-case' AS step, (SELECT CAST(value AS sysname) FROM sys.database_scoped_configurations WHERE name = 'TIME_ZONE') AS stored_value,
       CURRENT_TIMEZONE_ID() AS tz_id;
--- 03.8 Lower-case id (is the name case-insensitive? what gets stored?)
step|stored_value|tz_id
----|------------|-----
after lower-case|central standard time|Central Standard Time

PRINT '--- 03.9 Inside an explicit transaction, then ROLLBACK (is the setting transactional?)';
ALTER DATABASE SCOPED CONFIGURATION SET TIME_ZONE = LOCAL;
BEGIN TRAN;
    ALTER DATABASE SCOPED CONFIGURATION SET TIME_ZONE = 'Tokyo Standard Time';
ROLLBACK;
SELECT 'after rollback' AS step, (SELECT CAST(value AS sysname) FROM sys.database_scoped_configurations WHERE name = 'TIME_ZONE') AS stored_value,
       CURRENT_TIMEZONE_ID() AS tz_id;
--- 03.9 Inside an explicit transaction, then ROLLBACK (is the setting transactional?)
Msg 226, Level 16, State 7, Server your-server, Line 4
ALTER DATABASE SCOPED CONFIGURATION statement not allowed within multi-statement transaction.
IF @@TRANCOUNT > 0 ROLLBACK;
PRINT '--- 03.10 FOR SECONDARY (no geo-replica exists here; checks syntax + value_for_secondary)';
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET TIME_ZONE = 'Pacific Standard Time';
SELECT name, value, value_for_secondary FROM sys.database_scoped_configurations WHERE name = 'TIME_ZONE';
Msg 12110, Level 16, State 8, Server your-server, Line 2
Statement 'ALTER DATABASE SCOPED CONFIGURATION' failed, because it attempted to set the 'TIME_ZONE' option for the secondaries replica while this option is only allowed to be set for the primary.
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET TIME_ZONE = PRIMARY;
Msg 156, Level 15, State 1, Server your-server, Line 1
Incorrect syntax near the keyword 'PRIMARY'.
PRINT '--- 03.11 THE VALID STATEMENT';
ALTER DATABASE SCOPED CONFIGURATION SET TIME_ZONE = 'Central Standard Time';
--- 03.11 THE VALID STATEMENT
PRINT '--- 03.12 Effect on the SAME session that ran the ALTER (no reconnect)';
SELECT name, value, value_for_secondary, is_value_default
FROM sys.database_scoped_configurations WHERE name = 'TIME_ZONE';
SELECT CURRENT_TIMEZONE_ID() AS tz_id, CURRENT_TIMEZONE() AS tz_display,
       GETDATE() AS getdate_, SYSDATETIMEOFFSET() AS sysdatetimeoffset_, GETUTCDATE() AS getutcdate_;
SELECT session_id, time_zone AS session_time_zone_column FROM sys.dm_exec_sessions WHERE session_id = @@SPID;
--- 03.12 Effect on the SAME session that ran the ALTER (no reconnect)
name|value|value_for_secondary|is_value_default
----|-----|-------------------|----------------
TIME_ZONE|Central Standard Time|NULL|0

tz_id|tz_display|getdate_|sysdatetimeoffset_|getutcdate_
-----|----------|--------|------------------|-----------
Central Standard Time|(UTC-06:00) Central Time (US & Canada)|2026-10-15 20:49:44.430|2026-10-15 20:49:44.4311133 -05:00|2026-10-16 01:49:44.430

session_id|session_time_zone_column
----------|------------------------
67|LOCAL

# sqlcmd exit code: 0