Runbook: Local Time Zone in Azure SQL Database

#!/usr/bin/env bash
# Runs every scenario in order and writes one log (.txt) per scenario/database to results/.
# Auth: Microsoft Entra ID through your current `az login` (the server is Entra-only).
# Order matters: 02 seeds legacy rows while the CST database is still UTC, 03 switches it to Central.
set -u
cd "$(dirname "$0")"
# Required: export SERVER=<server>.database.windows.net DB_UTC=<utc-database> DB_CST=<cst-database>
: "${SERVER:?Set SERVER, e.g. export SERVER=myserver.database.windows.net}"
: "${DB_UTC:?Set DB_UTC to the database that stays on UTC, e.g. export DB_UTC=lab-utc}"
: "${DB_CST:?Set DB_CST to the database that will be set to Central time, e.g. export DB_CST=lab-cst}"
export SERVER DB_UTC DB_CST
RESULTS=results
mkdir -p "$RESULTS"

run() {  # run <db> <script> <logname> [extra sqlcmd args]
  local db=$1 script=$2 log="$RESULTS/$3.txt"; shift 3
  echo ">> $script on $db -> $log"
  {
    echo "# script: $script | database: $db | started (UTC): $(date -u '+%Y-%m-%d %H:%M:%S')"
    sqlcmd -S "$SERVER" -d "$db" --authentication-method ActiveDirectoryAzCli -W -s"|" -e "$@" -i "$script"
    echo "# sqlcmd exit code: $?"
  } > "$log" 2>&1
}

q() {  # q <db> <logname> <query>  - single query on a NEW connection
  local db=$1 log="$RESULTS/$2.txt"
  echo ">> ad-hoc on $db -> $log"
  {
    echo "# query: $3 | database: $db | started (UTC): $(date -u '+%Y-%m-%d %H:%M:%S')"
    sqlcmd -S "$SERVER" -d "$db" --authentication-method ActiveDirectoryAzCli -W -s"|" -Q "$3"
    echo "# sqlcmd exit code: $?"
  } > "$log" 2>&1
}

run "$DB_UTC" scripts/00_discovery.sql              00_discovery__utc
run "$DB_CST" scripts/00_discovery.sql              00_discovery__cst
run "$DB_UTC" scripts/01_function_surface.sql       01_function_surface__utc
run "$DB_CST" scripts/01_function_surface.sql       01_function_surface__cst_before
run "$DB_CST" scripts/02_cst_setup_legacy_data.sql  02_setup_legacy_data__cst
sleep 2
run "$DB_CST" scripts/03_set_db_timezone.sql        03_set_db_timezone__cst
q   "$DB_CST" 03b_new_connection__cst "SET NOCOUNT ON; SELECT 'new connection' 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 session_time_zone;"
run "$DB_CST" scripts/01_function_surface.sql       04_function_surface__cst_after
run "$DB_CST" scripts/05_brownfield_mixing.sql      05_brownfield_mixing__cst
run "$DB_CST" scripts/06_session_override.sql       06_session_override__cst
q   "$DB_CST" 06b_new_connection_isolation__cst "SET NOCOUNT ON; SELECT 'new connection after 06' AS step, CURRENT_TIMEZONE_ID() AS tz_id;"
run "$DB_UTC" scripts/06_session_override.sql       06_session_override__utc
run "$DB_CST" scripts/07_module_scope.sql           07_module_scope__cst
run "$DB_CST" scripts/08_at_time_zone_and_dst.sql   08_at_time_zone_and_dst__cst
run "$DB_CST" scripts/09_sargability.sql            09_sargability__cst
run "$DB_CST" scripts/10_system_objects_utc.sql     10_system_objects_utc__cst
run "$DB_CST" scripts/11_temporal_tables.sql        11_temporal_tables__cst
run "$DB_CST" scripts/12a_waitfor_time_local.sql    12a_waitfor_time_local__cst -t 30
run "$DB_CST" scripts/12b_waitfor_time_utc.sql      12b_waitfor_time_utc__cst -t 30
run "$DB_CST" scripts/13_migration_etl.sql          13_migration_etl__cst
run "$DB_UTC" scripts/14_hybrid_utc_db.sql          14_hybrid_utc_db__utc
echo ">> scripts/15_bacpac_dacpac_known_issue.sh -> $RESULTS/15_bacpac_dacpac__cst.txt"
bash scripts/15_bacpac_dacpac_known_issue.sh > "$RESULTS/15_bacpac_dacpac__cst.txt" 2>&1
echo ">> scripts/15e_bacpac_without_set_time_zone_modules.sh -> $RESULTS/15e_bacpac_without_set_tz_modules__cst.txt"
bash scripts/15e_bacpac_without_set_time_zone_modules.sh > "$RESULTS/15e_bacpac_without_set_tz_modules__cst.txt" 2>&1
q   "$DB_UTC" 16_final_state__utc "SET NOCOUNT ON; SELECT DB_NAME() db, name, value FROM sys.database_scoped_configurations WHERE name='TIME_ZONE';"
q   "$DB_CST" 16_final_state__cst "SET NOCOUNT ON; SELECT DB_NAME() db, name, value FROM sys.database_scoped_configurations WHERE name='TIME_ZONE';"
echo "done. Errors per log:"
grep -c "^Msg " "$RESULTS"/*.txt

/* 00 - Discovery (run on BOTH databases, read-only)
   Goal: confirm the feature surface exists (catalog + DMV columns) and capture the starting state. */
SET NOCOUNT ON;
PRINT '=== 00 Discovery on ' + DB_NAME();
GO
SELECT @@VERSION AS engine_version;
GO
PRINT '--- Database-scoped TIME_ZONE setting (expected LOCAL = UTC by default)';
SELECT name, value, value_for_secondary, is_value_default
FROM sys.database_scoped_configurations WHERE name = 'TIME_ZONE';
GO
PRINT '--- Session-level time zone column in sys.dm_exec_sessions';
SELECT session_id, time_zone FROM sys.dm_exec_sessions WHERE session_id = @@SPID;
GO
PRINT '--- Effective time zone';
SELECT CURRENT_TIMEZONE_ID() AS tz_id, CURRENT_TIMEZONE() AS tz_display;
GO
PRINT '--- Time zones available (count) and the ones used in this lab';
SELECT COUNT(*) AS zones_available FROM sys.time_zone_info;
SELECT name, current_utc_offset, is_currently_dst
FROM sys.time_zone_info
WHERE name IN ('UTC', 'Central Standard Time', 'Central America Standard Time', 'Canada Central Standard Time',
               'Eastern Standard Time', 'Pacific Standard Time', 'India Standard Time',
               'Tokyo Standard Time', 'Nepal Standard Time', 'New Zealand Standard Time')
ORDER BY current_utc_offset;
GO

/* 01 - Function surface (run on BOTH databases; run on CST before AND after the time zone change)
   Goal: which date/time functions follow the effective time zone and which stay UTC.
   Expected per the article: GETDATE, SYSDATETIME, CURRENT_TIMESTAMP, CURRENT_DATE, SYSDATETIMEOFFSET,
   CURRENT_TIMEZONE, CURRENT_TIMEZONE_ID follow the setting; GETUTCDATE and SYSUTCDATETIME never do. */
SET NOCOUNT ON;
PRINT '=== 01 Function surface on ' + DB_NAME();
GO
SELECT DB_NAME() AS db,
       (SELECT CAST(value AS sysname) FROM sys.database_scoped_configurations WHERE name = 'TIME_ZONE') AS db_tz_config,
       CURRENT_TIMEZONE_ID() AS tz_id,
       CURRENT_TIMEZONE() AS tz_display;
GO
SELECT 'GETDATE()' AS fn, CONVERT(varchar(40), GETDATE(), 121) AS value UNION ALL
SELECT 'SYSDATETIME()', CONVERT(varchar(40), SYSDATETIME(), 121) UNION ALL
SELECT 'CURRENT_TIMESTAMP', CONVERT(varchar(40), CURRENT_TIMESTAMP, 121) UNION ALL
SELECT 'SYSDATETIMEOFFSET()', CONVERT(varchar(40), SYSDATETIMEOFFSET(), 121) UNION ALL
SELECT 'GETUTCDATE()', CONVERT(varchar(40), GETUTCDATE(), 121) UNION ALL
SELECT 'SYSUTCDATETIME()', CONVERT(varchar(40), SYSUTCDATETIME(), 121);
GO
PRINT '--- CURRENT_DATE (separate batch in case it is not supported)';
SELECT 'CURRENT_DATE' AS fn, CONVERT(varchar(40), CURRENT_DATE, 121) AS value,
       CONVERT(varchar(10), GETUTCDATE(), 121) AS utc_date;
GO
PRINT '--- Offsets: local minus UTC';
SELECT DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()) AS getdate_minus_getutcdate_min,
       DATEDIFF(MINUTE, SYSUTCDATETIME(), SYSDATETIME()) AS sysdatetime_minus_sysutcdatetime_min,
       DATEPART(TZOFFSET, SYSDATETIMEOFFSET()) AS sysdatetimeoffset_tzoffset_min;
GO

/* 02 - Setup on the CST database, while it is still UTC (TIME_ZONE = LOCAL)
   Creates schema tzlab and table tzlab.Orders, then inserts "legacy" rows the way a 5-year-old app would:
   naive datetime2 values coming from GETDATE() (UTC today).
   Three timestamp columns are captured on every row so we can prove what happened later:
     OrderDate        datetime2       DEFAULT GETDATE()            <- naive, follows the time zone setting
     OrderDateOffset  datetimeoffset  DEFAULT SYSDATETIMEOFFSET()  <- carries its own offset
     OrderDateUtc     datetime2       DEFAULT SYSUTCDATETIME()     <- ground truth, always UTC */
SET NOCOUNT ON;
PRINT '=== 02 Setup tzlab + legacy (UTC-era) data on ' + DB_NAME();
GO
-- Make the script re-runnable: start from UTC.
IF (SELECT CAST(value AS sysname) FROM sys.database_scoped_configurations WHERE name = 'TIME_ZONE') <> 'LOCAL'
    ALTER DATABASE SCOPED CONFIGURATION SET TIME_ZONE = LOCAL;
GO
IF SCHEMA_ID('tzlab') IS NULL EXEC ('CREATE SCHEMA tzlab');
GO
IF OBJECT_ID('tzlab.ProductPrice') IS NOT NULL AND OBJECTPROPERTY(OBJECT_ID('tzlab.ProductPrice'), 'TableTemporalType') = 2
    ALTER TABLE tzlab.ProductPrice SET (SYSTEM_VERSIONING = OFF);
DROP TABLE IF EXISTS tzlab.ProductPrice, tzlab.ProductPriceHistory, tzlab.Orders_Migrated, tzlab.Orders,
                     tzlab.Events, tzlab.TriggerAudit, tzlab.TriggerTarget;
GO
CREATE TABLE tzlab.Orders
(
    OrderID         int IDENTITY(1, 1) CONSTRAINT PK_tzlab_Orders PRIMARY KEY,
    Phase           varchar(20)       NOT NULL,   -- 'legacy-UTC' or 'after-CST'
    CustomerID      int               NOT NULL,
    OrderDate       datetime2(3)      NOT NULL CONSTRAINT DF_Orders_OrderDate       DEFAULT (GETDATE()),
    OrderDateOffset datetimeoffset(3) NOT NULL CONSTRAINT DF_Orders_OrderDateOffset DEFAULT (SYSDATETIMEOFFSET()),
    OrderDateUtc    datetime2(3)      NOT NULL CONSTRAINT DF_Orders_OrderDateUtc    DEFAULT (SYSUTCDATETIME()),
    TotalDue        money             NOT NULL
);
GO
-- 10 legacy orders over the last 100 minutes (10 minutes apart), oldest first.
WITH src AS (
    SELECT TOP (10) CustomerID, TotalDue, ROW_NUMBER() OVER (ORDER BY SalesOrderID) AS rn
    FROM SalesLT.SalesOrderHeader ORDER BY SalesOrderID
)
INSERT tzlab.Orders (Phase, CustomerID, OrderDate, OrderDateOffset, OrderDateUtc, TotalDue)
SELECT 'legacy-UTC', CustomerID,
       DATEADD(MINUTE, -10 * (11 - rn), GETDATE()),
       DATEADD(MINUTE, -10 * (11 - rn), SYSDATETIMEOFFSET()),
       DATEADD(MINUTE, -10 * (11 - rn), SYSUTCDATETIME()),
       TotalDue
FROM src ORDER BY rn;
GO
SELECT CURRENT_TIMEZONE_ID() AS tz_during_insert, COUNT(*) AS legacy_rows,
       MIN(OrderDate) AS min_OrderDate, MAX(OrderDate) AS max_OrderDate
FROM tzlab.Orders;
SELECT OrderID, Phase, OrderDate, OrderDateOffset, OrderDateUtc FROM tzlab.Orders ORDER BY OrderID;
GO

/* 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;
GO
PRINT '--- 03.1 EXPECT ERROR: abbreviation ''CST'' is not a Windows time zone id';
ALTER DATABASE SCOPED CONFIGURATION SET TIME_ZONE = 'CST';
GO
PRINT '--- 03.2 EXPECT ERROR: IANA id ''America/Chicago''';
ALTER DATABASE SCOPED CONFIGURATION SET TIME_ZONE = 'America/Chicago';
GO
PRINT '--- 03.3 EXPECT ERROR: friendly name ''Central Time''';
ALTER DATABASE SCOPED CONFIGURATION SET TIME_ZONE = 'Central Time';
GO
PRINT '--- 03.4 EXPECT ERROR: offset string ''-06:00''';
ALTER DATABASE SCOPED CONFIGURATION SET TIME_ZONE = '-06:00';
GO
PRINT '--- 03.5 EXPECT ERROR: unquoted value (what the BACPAC/DACPAC scripting bug produces)';
ALTER DATABASE SCOPED CONFIGURATION SET TIME_ZONE = Central Standard Time;
GO
PRINT '--- 03.6 EXPECT ERROR: curly quotes copied from the blog post';
ALTER DATABASE SCOPED CONFIGURATION SET TIME_ZONE = ‘Central Standard Time’;
GO
PRINT '--- 03.7 Variable instead of literal';
DECLARE @tz sysname = N'Central Standard Time';
ALTER DATABASE SCOPED CONFIGURATION SET TIME_ZONE = @tz;
GO
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;
GO
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;
GO
IF @@TRANCOUNT > 0 ROLLBACK;
GO
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';
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET TIME_ZONE = PRIMARY;
GO
PRINT '--- 03.11 THE VALID STATEMENT';
ALTER DATABASE SCOPED CONFIGURATION SET TIME_ZONE = 'Central Standard Time';
GO
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;
GO

/* 01 - Function surface (run on BOTH databases; run on CST before AND after the time zone change)
   Goal: which date/time functions follow the effective time zone and which stay UTC.
   Expected per the article: GETDATE, SYSDATETIME, CURRENT_TIMESTAMP, CURRENT_DATE, SYSDATETIMEOFFSET,
   CURRENT_TIMEZONE, CURRENT_TIMEZONE_ID follow the setting; GETUTCDATE and SYSUTCDATETIME never do. */
SET NOCOUNT ON;
PRINT '=== 01 Function surface on ' + DB_NAME();
GO
SELECT DB_NAME() AS db,
       (SELECT CAST(value AS sysname) FROM sys.database_scoped_configurations WHERE name = 'TIME_ZONE') AS db_tz_config,
       CURRENT_TIMEZONE_ID() AS tz_id,
       CURRENT_TIMEZONE() AS tz_display;
GO
SELECT 'GETDATE()' AS fn, CONVERT(varchar(40), GETDATE(), 121) AS value UNION ALL
SELECT 'SYSDATETIME()', CONVERT(varchar(40), SYSDATETIME(), 121) UNION ALL
SELECT 'CURRENT_TIMESTAMP', CONVERT(varchar(40), CURRENT_TIMESTAMP, 121) UNION ALL
SELECT 'SYSDATETIMEOFFSET()', CONVERT(varchar(40), SYSDATETIMEOFFSET(), 121) UNION ALL
SELECT 'GETUTCDATE()', CONVERT(varchar(40), GETUTCDATE(), 121) UNION ALL
SELECT 'SYSUTCDATETIME()', CONVERT(varchar(40), SYSUTCDATETIME(), 121);
GO
PRINT '--- CURRENT_DATE (separate batch in case it is not supported)';
SELECT 'CURRENT_DATE' AS fn, CONVERT(varchar(40), CURRENT_DATE, 121) AS value,
       CONVERT(varchar(10), GETUTCDATE(), 121) AS utc_date;
GO
PRINT '--- Offsets: local minus UTC';
SELECT DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()) AS getdate_minus_getutcdate_min,
       DATEDIFF(MINUTE, SYSUTCDATETIME(), SYSDATETIME()) AS sysdatetime_minus_sysutcdatetime_min,
       DATEPART(TZOFFSET, SYSDATETIMEOFFSET()) AS sysdatetimeoffset_tzoffset_min;
GO

/* 05 - Brownfield danger: legacy UTC rows + new CST rows in the same naive datetime2 column
   Runs on the CST database AFTER script 03 set TIME_ZONE = 'Central Standard Time'.
   The app is unchanged: it still relies on DEFAULT GETDATE(). */
SET NOCOUNT ON;
PRINT '=== 05 Brownfield mixing on ' + DB_NAME() + ' (effective tz: ' + CURRENT_TIMEZONE_ID() + ')';
GO
-- 5 new orders, 1 second apart, using the column defaults exactly like the legacy app does.
DECLARE @i int = 0, @cust int, @due money;
WHILE @i < 5
BEGIN
    SELECT @cust = CustomerID, @due = TotalDue
    FROM SalesLT.SalesOrderHeader ORDER BY SalesOrderID DESC OFFSET @i ROWS FETCH NEXT 1 ROW ONLY;
    INSERT tzlab.Orders (Phase, CustomerID, TotalDue) VALUES ('after-CST', @cust, @due);
    WAITFOR DELAY '00:00:01';
    SET @i += 1;
END;
GO
PRINT '--- 05.1 True insert order (OrderID) with the three captured timestamps';
SELECT OrderID, Phase, OrderDate, OrderDateOffset, OrderDateUtc,
       DATEDIFF(MINUTE, OrderDateUtc, OrderDate) AS naive_minus_utc_min
FROM tzlab.Orders ORDER BY OrderID;
GO
PRINT '--- 05.2 ORDER BY OrderDate (naive): newest orders now sort BEFORE the legacy ones';
SELECT OrderID, Phase, OrderDate FROM tzlab.Orders ORDER BY OrderDate;
GO
PRINT '--- 05.3 "Latest order" via MAX(OrderDate) returns a legacy row, not the real latest';
SELECT TOP (1) OrderID, Phase, OrderDate AS latest_by_naive_date FROM tzlab.Orders ORDER BY OrderDate DESC;
SELECT TOP (1) OrderID, Phase, OrderDateUtc AS latest_by_utc FROM tzlab.Orders ORDER BY OrderDateUtc DESC;
GO
PRINT '--- 05.4 Rows that look like they are in the FUTURE (OrderDate > GETDATE())';
SELECT COUNT(*) AS future_rows, GETDATE() AS getdate_now FROM tzlab.Orders WHERE OrderDate > GETDATE();
GO
PRINT '--- 05.5 "Orders placed today" using the local date';
SELECT Phase, COUNT(*) AS rows_counted_as_today
FROM tzlab.Orders WHERE CAST(OrderDate AS date) = CAST(GETDATE() AS date) GROUP BY Phase;
SELECT CAST(GETDATE() AS date) AS local_today, CAST(GETUTCDATE() AS date) AS utc_today;
GO
PRINT '--- 05.6 "Orders in the last hour" (real answer: rows whose UTC time is within 60 min)';
SELECT 'naive OrderDate >= GETDATE()-1h' AS method, COUNT(*) AS rows_returned
FROM tzlab.Orders WHERE OrderDate >= DATEADD(HOUR, -1, GETDATE())
UNION ALL
SELECT 'OrderDateOffset >= SYSDATETIMEOFFSET()-1h', COUNT(*)
FROM tzlab.Orders WHERE OrderDateOffset >= DATEADD(HOUR, -1, SYSDATETIMEOFFSET())
UNION ALL
SELECT 'ground truth OrderDateUtc >= SYSUTCDATETIME()-1h', COUNT(*)
FROM tzlab.Orders WHERE OrderDateUtc >= DATEADD(HOUR, -1, SYSUTCDATETIME());
GO
PRINT '--- 05.7 datetimeoffset column keeps correct chronological order';
SELECT OrderID, Phase, OrderDateOffset FROM tzlab.Orders ORDER BY OrderDateOffset;
SELECT CASE WHEN EXISTS (
         SELECT 1 FROM (SELECT OrderID, ROW_NUMBER() OVER (ORDER BY OrderDateOffset, OrderID) AS rn_off,
                                        ROW_NUMBER() OVER (ORDER BY OrderID) AS rn_id FROM tzlab.Orders) x
         WHERE rn_off <> rn_id) THEN 'NO' ELSE 'YES' END AS datetimeoffset_order_matches_insert_order,
       CASE WHEN EXISTS (
         SELECT 1 FROM (SELECT OrderID, ROW_NUMBER() OVER (ORDER BY OrderDate, OrderID) AS rn_naive,
                                        ROW_NUMBER() OVER (ORDER BY OrderID) AS rn_id FROM tzlab.Orders) x
         WHERE rn_naive <> rn_id) THEN 'NO' ELSE 'YES' END AS naive_order_matches_insert_order;
GO

/* 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();
GO
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;
GO
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;
GO
SELECT '06.2 next batch (does it persist?)' AS step, CURRENT_TIMEZONE_ID() AS tz_id, SYSDATETIMEOFFSET() AS now_offset;
GO
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;
GO
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;
GO
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;
GO
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;
GO
PRINT '--- 06.7 EXPECT ERROR: abbreviation ''IST''';
SET TIME ZONE 'IST';
GO
PRINT '--- 06.8 EXPECT ERROR: IANA ''Asia/Kolkata''';
SET TIME ZONE 'Asia/Kolkata';
GO
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;
GO
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;
GO
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;
GO
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;
GO
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;
GO
SET TIME ZONE LOCAL;
GO
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;
GO
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;
GO
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;
GO

/* 07 - Module scope of SET TIME ZONE (CST database)
   Article claim: SET TIME ZONE inside a stored procedure or dynamic SQL reverts when the module exits.
   Also probes: nested procs, a proc that fails after SET, triggers, and scalar functions. */
SET NOCOUNT ON;
PRINT '=== 07 Module scope on ' + DB_NAME();
GO
CREATE OR ALTER PROCEDURE tzlab.usp_Nested
AS
BEGIN
    SET NOCOUNT ON;
    SET TIME ZONE 'Nepal Standard Time';
    SELECT '   nested proc (set Nepal)' AS step, CURRENT_TIMEZONE_ID() AS tz_id, SYSDATETIMEOFFSET() AS now_offset;
END;
GO
CREATE OR ALTER PROCEDURE tzlab.usp_ReportInTokyo
AS
BEGIN
    SET NOCOUNT ON;
    SELECT '  outer proc on entry' AS step, CURRENT_TIMEZONE_ID() AS tz_id, SYSDATETIMEOFFSET() AS now_offset;
    SET TIME ZONE 'Tokyo Standard Time';
    SELECT '  outer proc after SET Tokyo' AS step, CURRENT_TIMEZONE_ID() AS tz_id, SYSDATETIMEOFFSET() AS now_offset;
    EXEC tzlab.usp_Nested;
    SELECT '  outer proc after nested returns' AS step, CURRENT_TIMEZONE_ID() AS tz_id, SYSDATETIMEOFFSET() AS now_offset;
END;
GO
CREATE OR ALTER PROCEDURE tzlab.usp_SetThenFail
AS
BEGIN
    SET NOCOUNT ON;
    SET TIME ZONE 'Tokyo Standard Time';
    SELECT '  failing proc after SET Tokyo' AS step, CURRENT_TIMEZONE_ID() AS tz_id;
    THROW 50001, 'Simulated failure after SET TIME ZONE', 1;
END;
GO
CREATE OR ALTER PROCEDURE tzlab.usp_NoSet
AS
BEGIN
    SET NOCOUNT ON;
    SELECT '  proc without SET inherits caller' AS step, CURRENT_TIMEZONE_ID() AS tz_id, SYSDATETIMEOFFSET() AS now_offset;
END;
GO
PRINT '--- 07.1 Caller on DB setting -> proc sets Tokyo -> nested sets Nepal';
SELECT '07.1 before EXEC' AS step, CURRENT_TIMEZONE_ID() AS tz_id;
EXEC tzlab.usp_ReportInTokyo;
SELECT '07.1 after EXEC (expect DB setting)' AS step, CURRENT_TIMEZONE_ID() AS tz_id;
GO
PRINT '--- 07.2 Caller session overridden to India -> after EXEC should be India again';
SET TIME ZONE 'India Standard Time';
EXEC tzlab.usp_ReportInTokyo;
SELECT '07.2 after EXEC (expect India)' AS step, CURRENT_TIMEZONE_ID() AS tz_id;
GO
PRINT '--- 07.3 A proc that does not SET inherits the caller (India)';
EXEC tzlab.usp_NoSet;
SET TIME ZONE LOCAL;
GO
PRINT '--- 07.4 Proc that THROWs after SET TIME ZONE (caught by caller)';
BEGIN TRY
    EXEC tzlab.usp_SetThenFail;
END TRY
BEGIN CATCH
    SELECT '07.4 in CATCH' AS step, ERROR_NUMBER() AS err_no, ERROR_MESSAGE() AS err_msg, CURRENT_TIMEZONE_ID() AS tz_id;
END CATCH;
SELECT '07.4 after TRY/CATCH (expect DB setting)' AS step, CURRENT_TIMEZONE_ID() AS tz_id;
GO
PRINT '--- 07.5 Dynamic SQL via sp_executesql';
EXEC sys.sp_executesql N'SET TIME ZONE ''UTC''; SELECT ''  inside sp_executesql'' AS step, CURRENT_TIMEZONE_ID() AS tz_id, SYSDATETIMEOFFSET() AS now_offset;';
SELECT '07.5 after sp_executesql' AS step, CURRENT_TIMEZONE_ID() AS tz_id;
GO
PRINT '--- 07.6 Dynamic SQL via EXEC(string)';
EXEC (N'SET TIME ZONE ''UTC''; SELECT ''  inside EXEC()'' AS step, CURRENT_TIMEZONE_ID() AS tz_id;');
SELECT '07.6 after EXEC()' AS step, CURRENT_TIMEZONE_ID() AS tz_id;
GO
PRINT '--- 07.7 Trigger that SETs TIME ZONE UTC and writes GETDATE() into an audit row';
DROP TABLE IF EXISTS tzlab.TriggerAudit, tzlab.TriggerTarget;
CREATE TABLE tzlab.TriggerTarget (id int IDENTITY PRIMARY KEY, note varchar(50), created_local datetime2(3) DEFAULT (GETDATE()));
CREATE TABLE tzlab.TriggerAudit  (id int IDENTITY PRIMARY KEY, target_id int, audit_time datetime2(3), audit_tz sysname);
GO
CREATE OR ALTER TRIGGER tzlab.trg_TriggerTarget_Audit ON tzlab.TriggerTarget AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    SET TIME ZONE 'UTC';
    INSERT tzlab.TriggerAudit (target_id, audit_time, audit_tz) SELECT id, GETDATE(), CURRENT_TIMEZONE_ID() FROM inserted;
END;
GO
INSERT tzlab.TriggerTarget (note) VALUES ('row inserted from CST session');
SELECT '07.7 after INSERT (expect DB setting)' AS step, CURRENT_TIMEZONE_ID() AS tz_id;
SELECT t.id, t.note, t.created_local, a.audit_time, a.audit_tz FROM tzlab.TriggerTarget t JOIN tzlab.TriggerAudit a ON a.target_id = t.id;
GO
PRINT '--- 07.8 EXPECT ERROR?: SET TIME ZONE inside a scalar function (side-effecting statement)';
GO
CREATE OR ALTER FUNCTION tzlab.fn_NowInTokyo()
RETURNS datetimeoffset
AS
BEGIN
    SET TIME ZONE 'Tokyo Standard Time';
    RETURN SYSDATETIMEOFFSET();
END;
GO
PRINT '--- 07.9 Scalar function WITHOUT SET: does it see the caller session zone?';
GO
CREATE OR ALTER FUNCTION tzlab.fn_Now() RETURNS datetimeoffset AS BEGIN RETURN SYSDATETIMEOFFSET(); END;
GO
SET TIME ZONE 'India Standard Time';
SELECT '07.9 fn_Now() under India session' AS step, tzlab.fn_Now() AS fn_value, SYSDATETIMEOFFSET() AS direct_value;
SET TIME ZONE LOCAL;
GO
PRINT '--- 07.10 Native SET option check: does a module get its own SET TIME ZONE "snapshot" at CREATE time (like ANSI_NULLS)?';
SELECT OBJECT_NAME(object_id) AS module, uses_ansi_nulls, uses_quoted_identifier
FROM sys.sql_modules WHERE object_id = OBJECT_ID('tzlab.usp_NoSet');
GO

/* 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() + ')';
GO
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;
GO
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;
GO
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;
GO
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;
GO
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;
GO
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;
GO
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);
GO
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);
GO

/* 09 - SARGability (CST database)
   Table tzlab.Events stores UTC (the recommended "UTC at rest" design), ~200k rows over 30 days,
   nonclustered index on EventTimeUtc. Question: "how many events since local midnight today?"
     A) AT TIME ZONE applied to the COLUMN            -> correct, but is it a scan?
     B) convert the local boundary to UTC ONCE        -> correct and seekable
     C) compare the UTC column to GETDATE()-based local boundary (the gdoc's "just use GETDATE()" idea)
        -> seekable but WRONG when the column holds UTC, because GETDATE() is now Central time
     D) same as C but on a column stored in LOCAL time -> seekable and correct (the case the gdoc means) */
SET NOCOUNT ON;
PRINT '=== 09 SARGability on ' + DB_NAME() + ' (effective tz: ' + CURRENT_TIMEZONE_ID() + ')';
GO
DROP TABLE IF EXISTS tzlab.Events;
CREATE TABLE tzlab.Events
(
    EventID       int IDENTITY(1, 1) CONSTRAINT PK_tzlab_Events PRIMARY KEY,
    EventTimeUtc  datetime2(3) NOT NULL,
    EventTimeLocal datetime2(3) NOT NULL,   -- same instant, stored as naive Central time
    Payload       char(200)    NOT NULL DEFAULT ('x')
);
GO
-- ~200k events evenly spread over the last 30 days (UTC), with the matching local value.
WITH n AS (
    SELECT TOP (200000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS i
    FROM sys.all_objects a CROSS JOIN sys.all_objects b
)
INSERT tzlab.Events (EventTimeUtc, EventTimeLocal)
SELECT u, CAST(u AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time' AS datetime2(3))
FROM (SELECT DATEADD(SECOND, -CAST(i * 12.96 AS int), SYSUTCDATETIME()) AS u FROM n) x;
CREATE INDEX IX_Events_EventTimeUtc   ON tzlab.Events (EventTimeUtc);
CREATE INDEX IX_Events_EventTimeLocal ON tzlab.Events (EventTimeLocal);
GO
SELECT COUNT(*) AS rows_loaded, MIN(EventTimeUtc) AS min_utc, MAX(EventTimeUtc) AS max_utc FROM tzlab.Events;
GO
PRINT '--- 09.0 Boundaries';
SELECT CAST(CAST(GETDATE() AS date) AS datetime2(3)) AS local_midnight_naive,
       CAST(CAST(CAST(GETDATE() AS date) AS datetime2(3)) AT TIME ZONE CURRENT_TIMEZONE_ID() AT TIME ZONE 'UTC' AS datetime2(3)) AS local_midnight_in_utc;
GO
PRINT '--- 09.1 Estimated plans (SHOWPLAN_TEXT, not executed)';
GO
SET SHOWPLAN_TEXT ON;
GO
-- A
SELECT COUNT(*) FROM tzlab.Events
WHERE EventTimeUtc AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time' >= CAST(CAST(GETDATE() AS date) AS datetime2(3)) AT TIME ZONE 'Central Standard Time';
GO
-- B
DECLARE @fromUtc datetime2(3) = CAST(CAST(CAST(GETDATE() AS date) AS datetime2(3)) AT TIME ZONE CURRENT_TIMEZONE_ID() AT TIME ZONE 'UTC' AS datetime2(3));
SELECT COUNT(*) FROM tzlab.Events WHERE EventTimeUtc >= @fromUtc;
GO
-- C
SELECT COUNT(*) FROM tzlab.Events WHERE EventTimeUtc >= CAST(CAST(GETDATE() AS date) AS datetime2(3));
GO
-- D
SELECT COUNT(*) FROM tzlab.Events WHERE EventTimeLocal >= CAST(CAST(GETDATE() AS date) AS datetime2(3));
GO
SET SHOWPLAN_TEXT OFF;
GO
PRINT '--- 09.2 Executed with STATISTICS IO (logical reads appear in the messages)';
SET STATISTICS IO ON;
GO
PRINT 'A) AT TIME ZONE on the column';
SELECT 'A' AS q, COUNT(*) AS cnt FROM tzlab.Events
WHERE EventTimeUtc AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time' >= CAST(CAST(GETDATE() AS date) AS datetime2(3)) AT TIME ZONE 'Central Standard Time';
GO
PRINT 'B) boundary converted to UTC once';
DECLARE @fromUtc datetime2(3) = CAST(CAST(CAST(GETDATE() AS date) AS datetime2(3)) AT TIME ZONE CURRENT_TIMEZONE_ID() AT TIME ZONE 'UTC' AS datetime2(3));
SELECT 'B' AS q, COUNT(*) AS cnt FROM tzlab.Events WHERE EventTimeUtc >= @fromUtc;
GO
PRINT 'C) UTC column vs local GETDATE() boundary (seekable but semantically wrong)';
SELECT 'C' AS q, COUNT(*) AS cnt FROM tzlab.Events WHERE EventTimeUtc >= CAST(CAST(GETDATE() AS date) AS datetime2(3));
GO
PRINT 'D) LOCAL column vs local GETDATE() boundary';
SELECT 'D' AS q, COUNT(*) AS cnt FROM tzlab.Events WHERE EventTimeLocal >= CAST(CAST(GETDATE() AS date) AS datetime2(3));
GO
SET STATISTICS IO OFF;
GO

/* 10 - Catalog views, DMVs and Query Store stay in UTC (CST database)
   The session is additionally set to India Standard Time so any accidental "follows the setting"
   behaviour would show up as a +05:30 shift instead of the DB's -05:00. */
SET NOCOUNT ON;
SET TIME ZONE 'India Standard Time';
PRINT '=== 10 System objects on ' + DB_NAME() + ' (session tz: ' + CURRENT_TIMEZONE_ID() + ')';
GO
SELECT 'reference' AS what, GETDATE() AS getdate_local, GETUTCDATE() AS getutcdate_;
GO
PRINT '--- 10.1 sys.dm_exec_sessions (login_time, last_request_start_time)';
SELECT session_id, login_time, last_request_start_time, time_zone,
       DATEDIFF(MINUTE, last_request_start_time, GETUTCDATE()) AS min_vs_utc,
       DATEDIFF(MINUTE, last_request_start_time, GETDATE())    AS min_vs_local
FROM sys.dm_exec_sessions WHERE session_id = @@SPID;
GO
PRINT '--- 10.2 sys.dm_exec_requests.start_time for the running request';
SELECT start_time, GETUTCDATE() AS utc_now, GETDATE() AS local_now FROM sys.dm_exec_requests WHERE session_id = @@SPID;
GO
PRINT '--- 10.3 sys.objects.create_date for an object created right now';
DROP TABLE IF EXISTS tzlab.CreateDateProbe;
CREATE TABLE tzlab.CreateDateProbe (id int);
SELECT name, create_date, modify_date, GETUTCDATE() AS utc_now, GETDATE() AS local_now
FROM sys.objects WHERE object_id = OBJECT_ID('tzlab.CreateDateProbe');
DROP TABLE tzlab.CreateDateProbe;
GO
PRINT '--- 10.4 Query Store intervals (datetimeoffset) and last execution times';
SELECT TOP (3) runtime_stats_interval_id, start_time, end_time FROM sys.query_store_runtime_stats_interval ORDER BY runtime_stats_interval_id DESC;
SELECT TOP (3) q.query_id, q.last_execution_time FROM sys.query_store_query q ORDER BY q.last_execution_time DESC;
GO
PRINT '--- 10.5 sys.dm_db_resource_stats.end_time';
SELECT TOP (1) end_time, GETUTCDATE() AS utc_now, GETDATE() AS local_now FROM sys.dm_db_resource_stats ORDER BY end_time DESC;
GO
PRINT '--- 10.6 sys.dm_exec_query_stats.last_execution_time for this statement';
SELECT TOP (1) last_execution_time, GETUTCDATE() AS utc_now, GETDATE() AS local_now
FROM sys.dm_exec_query_stats ORDER BY last_execution_time DESC;
GO
PRINT '--- 10.7 sys.database_scoped_configurations / sys.databases have no "local" timestamps; sys.databases.create_date:';
SELECT name, create_date FROM sys.databases WHERE name = DB_NAME();
GO
SET TIME ZONE LOCAL;
GO

/* 11 - System-versioned temporal tables (CST database)
   Article claim: period columns (ValidFrom/ValidTo) are always UTC; FOR SYSTEM_TIME AS OF must be given UTC. */
SET NOCOUNT ON;
PRINT '=== 11 Temporal tables on ' + DB_NAME() + ' (effective tz: ' + CURRENT_TIMEZONE_ID() + ')';
GO
IF OBJECT_ID('tzlab.ProductPrice') IS NOT NULL AND OBJECTPROPERTY(OBJECT_ID('tzlab.ProductPrice'), 'TableTemporalType') = 2
    ALTER TABLE tzlab.ProductPrice SET (SYSTEM_VERSIONING = OFF);
DROP TABLE IF EXISTS tzlab.ProductPrice, tzlab.ProductPriceHistory;
GO
CREATE TABLE tzlab.ProductPrice
(
    ProductID int   NOT NULL CONSTRAINT PK_tzlab_ProductPrice PRIMARY KEY,
    ListPrice money NOT NULL,
    ChangedAtLocal datetime2(3) NOT NULL DEFAULT (GETDATE()),
    ValidFrom datetime2(3) GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo   datetime2(3) GENERATED ALWAYS AS ROW END   NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = tzlab.ProductPriceHistory));
GO
INSERT tzlab.ProductPrice (ProductID, ListPrice)
SELECT TOP (3) ProductID, ListPrice FROM SalesLT.Product ORDER BY ProductID;
GO
PRINT '--- 11.1 Period column vs GETDATE() default on the same row';
SELECT ProductID, ListPrice, ChangedAtLocal, ValidFrom, GETDATE() AS local_now, GETUTCDATE() AS utc_now,
       DATEDIFF(MINUTE, ChangedAtLocal, ValidFrom) AS validfrom_minus_local_min
FROM tzlab.ProductPrice;
GO
-- Capture "the moment before the price change" in three ways, then change the price.
WAITFOR DELAY '00:00:02';
DROP TABLE IF EXISTS #t;
SELECT SYSDATETIME() AS t_local, SYSUTCDATETIME() AS t_utc, SYSDATETIMEOFFSET() AS t_offset INTO #t;
WAITFOR DELAY '00:00:02';
UPDATE tzlab.ProductPrice SET ListPrice = ListPrice * 1.10, ChangedAtLocal = GETDATE();
SELECT * FROM #t;
GO
PRINT '--- 11.2 AS OF <local SYSDATETIME()>   (expect: 0 rows - local time is 5h before the rows existed)';
DECLARE @t datetime2(7) = (SELECT t_local FROM #t);
SELECT 'AS OF local' AS q, ProductID, ListPrice, ValidFrom, ValidTo FROM tzlab.ProductPrice FOR SYSTEM_TIME AS OF @t;
GO
PRINT '--- 11.3 AS OF <SYSUTCDATETIME()>      (expect: the ORIGINAL prices)';
DECLARE @t datetime2(7) = (SELECT t_utc FROM #t);
SELECT 'AS OF utc' AS q, ProductID, ListPrice, ValidFrom, ValidTo FROM tzlab.ProductPrice FOR SYSTEM_TIME AS OF @t;
GO
PRINT '--- 11.4 AS OF <SYSDATETIMEOFFSET()> passed directly (implicit conversion keeps local clock, drops offset?)';
DECLARE @t datetimeoffset(7) = (SELECT t_offset FROM #t);
SELECT 'AS OF datetimeoffset' AS q, ProductID, ListPrice, ValidFrom, ValidTo FROM tzlab.ProductPrice FOR SYSTEM_TIME AS OF @t;
GO
PRINT '--- 11.5 AS OF <datetimeoffset AT TIME ZONE ''UTC''> (correct pattern)';
DECLARE @t datetime2(7) = (SELECT CAST(t_offset AT TIME ZONE 'UTC' AS datetime2(7)) FROM #t);
SELECT 'AS OF offset->UTC' AS q, ProductID, ListPrice, ValidFrom, ValidTo FROM tzlab.ProductPrice FOR SYSTEM_TIME AS OF @t;
GO
PRINT '--- 11.6 History rows: ValidFrom/ValidTo in UTC vs ChangedAtLocal in Central';
SELECT 'history' AS src, ProductID, ListPrice, ChangedAtLocal, ValidFrom, ValidTo FROM tzlab.ProductPriceHistory
UNION ALL
SELECT 'current', ProductID, ListPrice, ChangedAtLocal, ValidFrom, ValidTo FROM tzlab.ProductPrice
ORDER BY ProductID, ValidFrom;
GO
PRINT '--- 11.7 Session override to Tokyo does not move ValidFrom either';
SET TIME ZONE 'Tokyo Standard Time';
UPDATE tzlab.ProductPrice SET ListPrice = ListPrice + 1, ChangedAtLocal = GETDATE() WHERE ProductID = (SELECT MIN(ProductID) FROM tzlab.ProductPrice);
SELECT ProductID, ChangedAtLocal AS changed_at_tokyo, ValidFrom, GETUTCDATE() AS utc_now
FROM tzlab.ProductPrice WHERE ProductID = (SELECT MIN(ProductID) FROM tzlab.ProductPrice);
SET TIME ZONE LOCAL;
GO

/* 12a - WAITFOR TIME with a LOCAL (Central) target 5 seconds ahead (CST database)
   If WAITFOR TIME interprets the target in the effective (Central) zone, this returns in ~5 s.
   If it interprets it as UTC, it would wait ~19 hours and the runner's 30 s query timeout fires. */
SET NOCOUNT ON;
PRINT '=== 12a WAITFOR TIME local target on ' + DB_NAME() + ' (effective tz: ' + CURRENT_TIMEZONE_ID() + ')';
DECLARE @target varchar(12) = CONVERT(varchar(12), DATEADD(SECOND, 5, GETDATE()), 114);
DECLARE @t0 datetime2(3) = SYSUTCDATETIME();
SELECT @target AS waitfor_target_local, GETDATE() AS local_now, GETUTCDATE() AS utc_now;
WAITFOR TIME @target;
SELECT 'returned' AS result, DATEDIFF(MILLISECOND, @t0, SYSUTCDATETIME()) AS waited_ms;
GO

/* 12b - WAITFOR TIME with a UTC target 5 seconds ahead (CST database)
   Mirror of 12a: if WAITFOR TIME follows the Central setting, a UTC target is ~19 hours in the past/future
   and the runner's 30 s timeout fires; if it is UTC-based, this returns in ~5 s. */
SET NOCOUNT ON;
PRINT '=== 12b WAITFOR TIME UTC target on ' + DB_NAME() + ' (effective tz: ' + CURRENT_TIMEZONE_ID() + ')';
DECLARE @target varchar(12) = CONVERT(varchar(12), DATEADD(SECOND, 5, GETUTCDATE()), 114);
DECLARE @t0 datetime2(3) = SYSUTCDATETIME();
SELECT @target AS waitfor_target_utc, GETDATE() AS local_now, GETUTCDATE() AS utc_now;
WAITFOR TIME @target;
SELECT 'returned' AS result, DATEDIFF(MILLISECOND, @t0, SYSUTCDATETIME()) AS waited_ms;
GO
PRINT '--- 12c WAITFOR DELAY is relative and unaffected';
DECLARE @t0 datetime2(3) = SYSUTCDATETIME();
WAITFOR DELAY '00:00:02';
SELECT 'WAITFOR DELAY 2s' AS test, DATEDIFF(MILLISECOND, @t0, SYSUTCDATETIME()) AS waited_ms;
GO

/* 13 - Brownfield fix: migrate the naive datetime2 column to datetimeoffset (CST database)
   Works on a COPY (tzlab.Orders_Migrated) so tzlab.Orders stays as evidence.
   Steps from the gdoc runbook: ALTER COLUMN -> +00:00 is stamped on every row -> re-tag rows correctly. */
SET NOCOUNT ON;
PRINT '=== 13 Migration ETL on ' + DB_NAME() + ' (effective tz: ' + CURRENT_TIMEZONE_ID() + ')';
GO
PRINT '--- 13.1 EXPECT ERROR: ALTER COLUMN on the real table fails because of the DEFAULT GETDATE() constraint';
BEGIN TRAN;
ALTER TABLE tzlab.Orders ALTER COLUMN OrderDate datetimeoffset(3) NOT NULL;
IF @@TRANCOUNT > 0 ROLLBACK;
GO
IF @@TRANCOUNT > 0 ROLLBACK;
GO
PRINT '--- 13.2 Work on a copy (SELECT INTO does not copy constraints)';
DROP TABLE IF EXISTS tzlab.Orders_Migrated;
SELECT * INTO tzlab.Orders_Migrated FROM tzlab.Orders;
ALTER TABLE tzlab.Orders_Migrated ALTER COLUMN OrderDate datetimeoffset(3) NOT NULL;
SELECT OrderID, Phase, OrderDate AS after_alter_column, OrderDateUtc FROM tzlab.Orders_Migrated ORDER BY OrderID;
PRINT '    -> every row got +00:00: right for legacy-UTC rows, WRONG for after-CST rows (they are Central clock values)';
GO
PRINT '--- 13.3 Re-tag the after-CST rows: reinterpret the clock value as Central time';
UPDATE tzlab.Orders_Migrated
SET OrderDate = CAST(OrderDate AS datetime2(3)) AT TIME ZONE 'Central Standard Time'
WHERE Phase = 'after-CST';
GO
PRINT '--- 13.4 Optional: express legacy rows in Central too (same instant, different offset)';
UPDATE tzlab.Orders_Migrated
SET OrderDate = OrderDate AT TIME ZONE 'Central Standard Time'
WHERE Phase = 'legacy-UTC';
GO
PRINT '--- 13.5 Verify: every row now equals the ground-truth UTC column';
SELECT OrderID, Phase, OrderDate, OrderDateUtc,
       DATEDIFF(MILLISECOND, OrderDateUtc, CAST(SWITCHOFFSET(OrderDate, '+00:00') AS datetime2(3))) AS diff_ms_vs_truth
FROM tzlab.Orders_Migrated ORDER BY OrderDate;
SELECT SUM(CASE WHEN ABS(DATEDIFF(MILLISECOND, OrderDateUtc, CAST(SWITCHOFFSET(OrderDate, '+00:00') AS datetime2(3)))) > 5 THEN 1 ELSE 0 END) AS rows_not_matching_truth,
       COUNT(*) AS total_rows
FROM tzlab.Orders_Migrated;
GO
PRINT '--- 13.6 The hard part in real life: without a Phase flag, which rows are UTC and which are local?';
PRINT '    Heuristic attempt: rows with OrderID > last legacy id. Works here only because we know the cut-over id.';
SELECT MAX(OrderID) AS last_legacy_id FROM tzlab.Orders WHERE Phase = 'legacy-UTC';
GO

/* 14 - Hybrid pattern on the UTC database: "UTC at rest, local in session" (read-only, nothing is created)
   The database setting stays LOCAL (UTC). A reporting session sets Central time for itself. */
SET NOCOUNT ON;
PRINT '=== 14 Hybrid pattern on ' + DB_NAME();
GO
SELECT 'db default' AS ctx, CURRENT_TIMEZONE_ID() AS tz_id, GETDATE() AS getdate_,
       (SELECT CAST(value AS sysname) FROM sys.database_scoped_configurations WHERE name = 'TIME_ZONE') AS db_tz_config;
GO
SET TIME ZONE 'Central Standard Time';
SELECT 'reporting session' AS ctx, CURRENT_TIMEZONE_ID() AS tz_id, GETDATE() AS getdate_,
       (SELECT CAST(value AS sysname) FROM sys.database_scoped_configurations WHERE name = 'TIME_ZONE') AS db_tz_config;
GO
PRINT '--- 14.1 Stored values do not change; display them in the session zone with AT TIME ZONE CURRENT_TIMEZONE_ID()';
SELECT TOP (5) SalesOrderID, ModifiedDate AS stored_utc,
       ModifiedDate AT TIME ZONE 'UTC' AT TIME ZONE CURRENT_TIMEZONE_ID() AS shown_in_session_zone
FROM SalesLT.SalesOrderHeader ORDER BY SalesOrderID;
GO
PRINT '--- 14.2 Local-day boundary converted to UTC once (SARGable pattern for UTC-at-rest data)';
DECLARE @localDay date = '2008-06-01';
DECLARE @fromUtc datetime2(3) = CAST(CAST(@localDay AS datetime2(3)) AT TIME ZONE CURRENT_TIMEZONE_ID() AT TIME ZONE 'UTC' AS datetime2(3));
DECLARE @toUtc   datetime2(3) = CAST(CAST(DATEADD(DAY, 1, @localDay) AS datetime2(3)) AT TIME ZONE CURRENT_TIMEZONE_ID() AT TIME ZONE 'UTC' AS datetime2(3));
SELECT @localDay AS local_day, @fromUtc AS from_utc, @toUtc AS to_utc,
       (SELECT COUNT(*) FROM SalesLT.SalesOrderHeader WHERE OrderDate >= @fromUtc AND OrderDate < @toUtc) AS orders_in_local_day,
       (SELECT COUNT(*) FROM SalesLT.SalesOrderHeader WHERE CAST(OrderDate AS date) = @localDay) AS orders_in_utc_day;
GO
SET TIME ZONE LOCAL;
SELECT 'after SET TIME ZONE LOCAL' AS ctx, CURRENT_TIMEZONE_ID() AS tz_id;
GO

#!/usr/bin/env bash
# 15 - Known issue: TIME_ZONE scoped configuration is scripted without quotes (BACPAC import / DACPAC deploy fail).
# Steps:
#   a) Extract a DACPAC from the CST database and look at how TIME_ZONE is stored in model.xml
#   b) Export a BACPAC (schema + data) from the CST database and look at its model.xml
#   c) Generate (do NOT run) a deploy script from that DACPAC against the UTC database and find the TIME_ZONE line
#   d) Parse-only check of that scripted line against the UTC database (SET PARSEONLY ON: nothing executes)
# Nothing is deployed or imported. Requires: sqlpackage (dotnet tool), sqlcmd, az login.
set -u
# Required: export SERVER=<server>.database.windows.net DB_UTC=<utc-database> DB_CST=<cst-database>
: "${SERVER:?Set SERVER, e.g. export SERVER=myserver.database.windows.net}"
: "${DB_UTC:?Set DB_UTC to the database that stays on UTC, e.g. export DB_UTC=lab-utc}"
: "${DB_CST:?Set DB_CST to the database that will be set to Central time, e.g. export DB_CST=lab-cst}"
OUT="${OUT:-$(cd "$(dirname "$0")/.." && pwd)/results/15_artifacts}"
SQLPACKAGE="${SQLPACKAGE:-$HOME/.dotnet/tools/sqlpackage}"
mkdir -p "$OUT"

TOKEN=$(az account get-access-token --resource https://database.windows.net --query accessToken -o tsv)

echo "=== 15a Extract DACPAC from $DB_CST"
"$SQLPACKAGE" /a:Extract /ssn:"$SERVER" /sdn:"$DB_CST" /at:"$TOKEN" /tf:"$OUT/cst.dacpac" /p:VerifyExtraction=false 2>&1 | tail -5
echo "--- TIME_ZONE in dacpac model.xml:"
unzip -p "$OUT/cst.dacpac" model.xml | perl -pe 's/></>\n</g' | grep -n -i -B2 -A4 "time_zone\|timezone" | head -40 || echo "(no TIME_ZONE entry found in model.xml)"

echo
echo "=== 15b Export BACPAC from $DB_CST"
"$SQLPACKAGE" /a:Export /ssn:"$SERVER" /sdn:"$DB_CST" /at:"$TOKEN" /tf:"$OUT/cst.bacpac" 2>&1 | tail -5
echo "--- TIME_ZONE in bacpac model.xml:"
unzip -p "$OUT/cst.bacpac" model.xml | perl -pe 's/></>\n</g' | grep -n -i -B2 -A4 "time_zone\|timezone" | head -40 || echo "(no TIME_ZONE entry found in model.xml)"

echo
echo "=== 15c Generate deploy script (not executed) from cst.dacpac against $DB_UTC"
"$SQLPACKAGE" /a:Script /sf:"$OUT/cst.dacpac" /tsn:"$SERVER" /tdn:"$DB_UTC" /at:"$TOKEN" /op:"$OUT/deploy_to_utc.sql" \
  /p:DropObjectsNotInSource=false /p:ScriptDatabaseOptions=true 2>&1 | tail -5
echo "--- TIME_ZONE lines in generated deploy script:"
grep -n -i "time_zone" "$OUT/deploy_to_utc.sql" || echo "(no TIME_ZONE line generated)"

echo
echo "=== 15d Parse-only check of the scripted TIME_ZONE line (SET PARSEONLY ON, nothing executes)"
LINE=$(grep -i -m1 "SET TIME_ZONE" "$OUT/deploy_to_utc.sql")
if [ -n "$LINE" ]; then
  printf 'SET PARSEONLY ON;\nGO\n%s\nGO\nSET PARSEONLY OFF;\nGO\n' "$LINE" > "$OUT/parse_check.sql"
  echo "statement under test: $LINE"
  sqlcmd -S "$SERVER" -d "$DB_UTC" --authentication-method ActiveDirectoryAzCli -i "$OUT/parse_check.sql" 2>&1
  echo "(no output above = parsed OK)"
else
  echo "skipped: no TIME_ZONE statement found in the deploy script"
fi
echo
echo "--- Control: same check with a correctly quoted statement"
printf "SET PARSEONLY ON;\nGO\nALTER DATABASE SCOPED CONFIGURATION SET TIME_ZONE = 'Central Standard Time';\nGO\nSET PARSEONLY OFF;\nGO\n" > "$OUT/parse_check_quoted.sql"
sqlcmd -S "$SERVER" -d "$DB_UTC" --authentication-method ActiveDirectoryAzCli -i "$OUT/parse_check_quoted.sql" 2>&1
echo "(no output above = parsed OK)"
echo "--- UTC database TIME_ZONE must still be LOCAL after the parse checks:"
sqlcmd -S "$SERVER" -d "$DB_UTC" --authentication-method ActiveDirectoryAzCli -W -Q "SET NOCOUNT ON; SELECT name, value FROM sys.database_scoped_configurations WHERE name='TIME_ZONE'" 2>&1

#!/usr/bin/env bash
# 15e - Isolate the two BACPAC problems seen in 15b:
#   1) sqlpackage cannot parse SET TIME ZONE inside procedures/triggers (export blocked with SQL46010)
#   2) the documented issue: TIME_ZONE scoped configuration is scripted without quotes
# Temporarily drops every module whose definition contains SET TIME ZONE, exports a BACPAC,
# inspects model.xml, then recreates the modules by re-running scripts/07_module_scope.sql.
set -u
cd "$(dirname "$0")/.."
# Required: export SERVER=<server>.database.windows.net DB_UTC=<utc-database> DB_CST=<cst-database>
: "${SERVER:?Set SERVER, e.g. export SERVER=myserver.database.windows.net}"
: "${DB_UTC:?Set DB_UTC to the database that stays on UTC, e.g. export DB_UTC=lab-utc}"
: "${DB_CST:?Set DB_CST to the database that will be set to Central time, e.g. export DB_CST=lab-cst}"
OUT="${OUT:-results/15_artifacts}"
SQLPACKAGE="${SQLPACKAGE:-$HOME/.dotnet/tools/sqlpackage}"
SQLCMD=(sqlcmd -S "$SERVER" -d "$DB_CST" --authentication-method ActiveDirectoryAzCli -W -s"|")
mkdir -p "$OUT"
TOKEN=$(az account get-access-token --resource https://database.windows.net --query accessToken -o tsv)

echo "=== 15e.1 Modules containing SET TIME ZONE"
"${SQLCMD[@]}" -Q "SET NOCOUNT ON; SELECT OBJECT_SCHEMA_NAME(m.object_id) + '.' + OBJECT_NAME(m.object_id) AS module, o.type_desc FROM sys.sql_modules m JOIN sys.objects o ON o.object_id = m.object_id WHERE m.definition LIKE '%SET TIME ZONE%';"

echo "=== 15e.2 Dropping them (recreated at the end)"
"${SQLCMD[@]}" -Q "SET NOCOUNT ON; DECLARE @sql nvarchar(max) = N''; SELECT @sql += CASE o.type WHEN 'TR' THEN N'DROP TRIGGER ' WHEN 'P' THEN N'DROP PROCEDURE ' ELSE N'DROP FUNCTION ' END + QUOTENAME(OBJECT_SCHEMA_NAME(o.object_id)) + N'.' + QUOTENAME(o.name) + N'; ' FROM sys.sql_modules m JOIN sys.objects o ON o.object_id = m.object_id WHERE m.definition LIKE N'%SET TIME ZONE%'; PRINT @sql; EXEC (@sql);"

echo "=== 15e.3 Export BACPAC again"
"$SQLPACKAGE" /a:Export /ssn:"$SERVER" /sdn:"$DB_CST" /at:"$TOKEN" /tf:"$OUT/cst_no_set_tz_modules.bacpac" 2>&1 | tail -4
echo "--- TIME_ZONE in bacpac model.xml:"
unzip -p "$OUT/cst_no_set_tz_modules.bacpac" model.xml | perl -pe 's/></>\n</g' | grep -n -B1 -A3 'GenericDatabaseScopedConfigurationOptions" Name="\[TIME_ZONE\]' || echo "(no TIME_ZONE entry found)"

echo "=== 15e.4 Import scripting is not executed here (a BACPAC import needs a NEW database)."
echo "    sqlpackage /a:Script only accepts a .dacpac, so the scripted TIME_ZONE line is shown by 15c (same script generator)."

echo "=== 15e.5 Recreate the dropped modules (re-run scripts/07_module_scope.sql)"
"${SQLCMD[@]}" -i scripts/07_module_scope.sql > "$OUT/07_rerun_after_15e.txt" 2>&1
"${SQLCMD[@]}" -Q "SET NOCOUNT ON; SELECT COUNT(*) AS modules_with_set_time_zone FROM sys.sql_modules WHERE definition LIKE '%SET TIME ZONE%';"

/* 99 - Cleanup (CST database). NOT run by run_all.sh - run it by hand when the lab is finished:
   sqlcmd -S <server>.database.windows.net -d <cst-database> --authentication-method ActiveDirectoryAzCli -i scripts/99_cleanup.sql */
SET NOCOUNT ON;
IF OBJECT_ID('tzlab.ProductPrice') IS NOT NULL AND OBJECTPROPERTY(OBJECT_ID('tzlab.ProductPrice'), 'TableTemporalType') = 2
    ALTER TABLE tzlab.ProductPrice SET (SYSTEM_VERSIONING = OFF);
DROP TABLE IF EXISTS tzlab.ProductPrice, tzlab.ProductPriceHistory, tzlab.Orders_Migrated, tzlab.Orders,
                     tzlab.Events, tzlab.TriggerAudit, tzlab.TriggerTarget, tzlab.CreateDateProbe;
DROP PROCEDURE IF EXISTS tzlab.usp_ReportInTokyo, tzlab.usp_Nested, tzlab.usp_SetThenFail, tzlab.usp_NoSet;
DROP FUNCTION IF EXISTS tzlab.fn_NowInTokyo, tzlab.fn_Now;
IF SCHEMA_ID('tzlab') IS NOT NULL DROP SCHEMA tzlab;
ALTER DATABASE SCOPED CONFIGURATION SET TIME_ZONE = LOCAL;
SELECT name, value FROM sys.database_scoped_configurations WHERE name = 'TIME_ZONE';
GO