# script: scripts/14_hybrid_utc_db.sql | database: lab-utc | started (UTC): 2026-10-16 01:50:54
/* 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();
=== 14 Hybrid pattern on lab-utc
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;
ctx|tz_id|getdate_|db_tz_config
---|-----|--------|------------
db default|UTC|2026-10-16 01:50:55.560|LOCAL

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;
ctx|tz_id|getdate_|db_tz_config
---|-----|--------|------------
reporting session|Central Standard Time|2026-10-15 20:50:55.603|LOCAL

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;
--- 14.1 Stored values do not change; display them in the session zone with AT TIME ZONE CURRENT_TIMEZONE_ID()
SalesOrderID|stored_utc|shown_in_session_zone
------------|----------|---------------------
71774|2008-06-08 00:00:00.000|2008-06-07 19:00:00.000 -05:00
71776|2008-06-08 00:00:00.000|2008-06-07 19:00:00.000 -05:00
71780|2008-06-08 00:00:00.000|2008-06-07 19:00:00.000 -05:00
71782|2008-06-08 00:00:00.000|2008-06-07 19:00:00.000 -05:00
71783|2008-06-08 00:00:00.000|2008-06-07 19:00:00.000 -05:00

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;
--- 14.2 Local-day boundary converted to UTC once (SARGable pattern for UTC-at-rest data)
local_day|from_utc|to_utc|orders_in_local_day|orders_in_utc_day
---------|--------|------|-------------------|-----------------
2008-06-01|2008-06-01 05:00:00.000|2008-06-02 05:00:00.000|0|32

SET TIME ZONE LOCAL;
SELECT 'after SET TIME ZONE LOCAL' AS ctx, CURRENT_TIMEZONE_ID() AS tz_id;
ctx|tz_id
---|-----
after SET TIME ZONE LOCAL|UTC

# sqlcmd exit code: 0