# script: scripts/05_brownfield_mixing.sql | database: lab-cst | started (UTC): 2026-10-16 01:49:45 /* 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() + ')'; === 05 Brownfield mixing on lab-cst (effective tz: Central Standard Time) -- 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; 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; --- 05.1 True insert order (OrderID) with the three captured timestamps OrderID|Phase|OrderDate|OrderDateOffset|OrderDateUtc|naive_minus_utc_min -------|-----|---------|---------------|------------|------------------- 1|legacy-UTC|2026-10-16 00:09:40.970|2026-10-16 00:09:40.974 +00:00|2026-10-16 00:09:40.974|0 2|legacy-UTC|2026-10-16 00:19:40.970|2026-10-16 00:19:40.974 +00:00|2026-10-16 00:19:40.974|0 3|legacy-UTC|2026-10-16 00:29:40.970|2026-10-16 00:29:40.974 +00:00|2026-10-16 00:29:40.974|0 4|legacy-UTC|2026-10-16 00:39:40.970|2026-10-16 00:39:40.974 +00:00|2026-10-16 00:39:40.974|0 5|legacy-UTC|2026-10-16 00:49:40.970|2026-10-16 00:49:40.974 +00:00|2026-10-16 00:49:40.974|0 6|legacy-UTC|2026-10-16 00:59:40.970|2026-10-16 00:59:40.974 +00:00|2026-10-16 00:59:40.974|0 7|legacy-UTC|2026-10-16 01:09:40.970|2026-10-16 01:09:40.974 +00:00|2026-10-16 01:09:40.974|0 8|legacy-UTC|2026-10-16 01:19:40.970|2026-10-16 01:19:40.974 +00:00|2026-10-16 01:19:40.974|0 9|legacy-UTC|2026-10-16 01:29:40.970|2026-10-16 01:29:40.974 +00:00|2026-10-16 01:29:40.974|0 10|legacy-UTC|2026-10-16 01:39:40.970|2026-10-16 01:39:40.974 +00:00|2026-10-16 01:39:40.974|0 11|after-CST|2026-10-15 20:49:46.557|2026-10-15 20:49:46.558 -05:00|2026-10-16 01:49:46.558|-300 12|after-CST|2026-10-15 20:49:47.570|2026-10-15 20:49:47.572 -05:00|2026-10-16 01:49:47.572|-300 13|after-CST|2026-10-15 20:49:48.577|2026-10-15 20:49:48.579 -05:00|2026-10-16 01:49:48.579|-300 14|after-CST|2026-10-15 20:49:49.590|2026-10-15 20:49:49.591 -05:00|2026-10-16 01:49:49.591|-300 15|after-CST|2026-10-15 20:49:50.603|2026-10-15 20:49:50.606 -05:00|2026-10-16 01:49:50.606|-300 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; --- 05.2 ORDER BY OrderDate (naive): newest orders now sort BEFORE the legacy ones OrderID|Phase|OrderDate -------|-----|--------- 11|after-CST|2026-10-15 20:49:46.557 12|after-CST|2026-10-15 20:49:47.570 13|after-CST|2026-10-15 20:49:48.577 14|after-CST|2026-10-15 20:49:49.590 15|after-CST|2026-10-15 20:49:50.603 1|legacy-UTC|2026-10-16 00:09:40.970 2|legacy-UTC|2026-10-16 00:19:40.970 3|legacy-UTC|2026-10-16 00:29:40.970 4|legacy-UTC|2026-10-16 00:39:40.970 5|legacy-UTC|2026-10-16 00:49:40.970 6|legacy-UTC|2026-10-16 00:59:40.970 7|legacy-UTC|2026-10-16 01:09:40.970 8|legacy-UTC|2026-10-16 01:19:40.970 9|legacy-UTC|2026-10-16 01:29:40.970 10|legacy-UTC|2026-10-16 01:39:40.970 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; --- 05.3 "Latest order" via MAX(OrderDate) returns a legacy row, not the real latest OrderID|Phase|latest_by_naive_date -------|-----|-------------------- 10|legacy-UTC|2026-10-16 01:39:40.970 OrderID|Phase|latest_by_utc -------|-----|------------- 15|after-CST|2026-10-16 01:49:50.606 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(); --- 05.4 Rows that look like they are in the FUTURE (OrderDate > GETDATE()) future_rows|getdate_now -----------|----------- 10|2026-10-15 20:49:51.820 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; --- 05.5 "Orders placed today" using the local date Phase|rows_counted_as_today -----|--------------------- after-CST|5 local_today|utc_today -----------|--------- 2026-10-15|2026-10-16 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()); --- 05.6 "Orders in the last hour" (real answer: rows whose UTC time is within 60 min) method|rows_returned ------|------------- naive OrderDate >= GETDATE()-1h|15 OrderDateOffset >= SYSDATETIMEOFFSET()-1h|10 ground truth OrderDateUtc >= SYSUTCDATETIME()-1h|10 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; --- 05.7 datetimeoffset column keeps correct chronological order OrderID|Phase|OrderDateOffset -------|-----|--------------- 1|legacy-UTC|2026-10-16 00:09:40.974 +00:00 2|legacy-UTC|2026-10-16 00:19:40.974 +00:00 3|legacy-UTC|2026-10-16 00:29:40.974 +00:00 4|legacy-UTC|2026-10-16 00:39:40.974 +00:00 5|legacy-UTC|2026-10-16 00:49:40.974 +00:00 6|legacy-UTC|2026-10-16 00:59:40.974 +00:00 7|legacy-UTC|2026-10-16 01:09:40.974 +00:00 8|legacy-UTC|2026-10-16 01:19:40.974 +00:00 9|legacy-UTC|2026-10-16 01:29:40.974 +00:00 10|legacy-UTC|2026-10-16 01:39:40.974 +00:00 11|after-CST|2026-10-15 20:49:46.558 -05:00 12|after-CST|2026-10-15 20:49:47.572 -05:00 13|after-CST|2026-10-15 20:49:48.579 -05:00 14|after-CST|2026-10-15 20:49:49.591 -05:00 15|after-CST|2026-10-15 20:49:50.606 -05:00 datetimeoffset_order_matches_insert_order|naive_order_matches_insert_order -----------------------------------------|-------------------------------- YES|NO # sqlcmd exit code: 0