# script: scripts/13_migration_etl.sql | database: lab-cst | started (UTC): 2026-10-16 01:50:54 /* 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() + ')'; === 13 Migration ETL on lab-cst (effective tz: Central Standard Time) 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; --- 13.1 EXPECT ERROR: ALTER COLUMN on the real table fails because of the DEFAULT GETDATE() constraint Msg 5074, Level 16, State 1, Server your-server, Line 3 The object 'DF_Orders_OrderDate' is dependent on column 'OrderDate'. Msg 4922, Level 16, State 9, Server your-server, Line 3 ALTER TABLE ALTER COLUMN OrderDate failed because one or more objects access this column. IF @@TRANCOUNT > 0 ROLLBACK; 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)'; --- 13.2 Work on a copy (SELECT INTO does not copy constraints) OrderID|Phase|after_alter_column|OrderDateUtc -------|-----|------------------|------------ 1|legacy-UTC|2026-10-16 00:09:40.970 +00:00|2026-10-16 00:09:40.974 2|legacy-UTC|2026-10-16 00:19:40.970 +00:00|2026-10-16 00:19:40.974 3|legacy-UTC|2026-10-16 00:29:40.970 +00:00|2026-10-16 00:29:40.974 4|legacy-UTC|2026-10-16 00:39:40.970 +00:00|2026-10-16 00:39:40.974 5|legacy-UTC|2026-10-16 00:49:40.970 +00:00|2026-10-16 00:49:40.974 6|legacy-UTC|2026-10-16 00:59:40.970 +00:00|2026-10-16 00:59:40.974 7|legacy-UTC|2026-10-16 01:09:40.970 +00:00|2026-10-16 01:09:40.974 8|legacy-UTC|2026-10-16 01:19:40.970 +00:00|2026-10-16 01:19:40.974 9|legacy-UTC|2026-10-16 01:29:40.970 +00:00|2026-10-16 01:29:40.974 10|legacy-UTC|2026-10-16 01:39:40.970 +00:00|2026-10-16 01:39:40.974 11|after-CST|2026-10-15 20:49:46.557 +00:00|2026-10-16 01:49:46.558 12|after-CST|2026-10-15 20:49:47.570 +00:00|2026-10-16 01:49:47.572 13|after-CST|2026-10-15 20:49:48.577 +00:00|2026-10-16 01:49:48.579 14|after-CST|2026-10-15 20:49:49.590 +00:00|2026-10-16 01:49:49.591 15|after-CST|2026-10-15 20:49:50.603 +00:00|2026-10-16 01:49:50.606 -> every row got +00:00: right for legacy-UTC rows, WRONG for after-CST rows (they are Central clock values) 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'; --- 13.3 Re-tag the after-CST rows: reinterpret the clock value as Central time 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'; --- 13.4 Optional: express legacy rows in Central too (same instant, different offset) 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; --- 13.5 Verify: every row now equals the ground-truth UTC column OrderID|Phase|OrderDate|OrderDateUtc|diff_ms_vs_truth -------|-----|---------|------------|---------------- 1|legacy-UTC|2026-10-15 19:09:40.970 -05:00|2026-10-16 00:09:40.974|-4 2|legacy-UTC|2026-10-15 19:19:40.970 -05:00|2026-10-16 00:19:40.974|-4 3|legacy-UTC|2026-10-15 19:29:40.970 -05:00|2026-10-16 00:29:40.974|-4 4|legacy-UTC|2026-10-15 19:39:40.970 -05:00|2026-10-16 00:39:40.974|-4 5|legacy-UTC|2026-10-15 19:49:40.970 -05:00|2026-10-16 00:49:40.974|-4 6|legacy-UTC|2026-10-15 19:59:40.970 -05:00|2026-10-16 00:59:40.974|-4 7|legacy-UTC|2026-10-15 20:09:40.970 -05:00|2026-10-16 01:09:40.974|-4 8|legacy-UTC|2026-10-15 20:19:40.970 -05:00|2026-10-16 01:19:40.974|-4 9|legacy-UTC|2026-10-15 20:29:40.970 -05:00|2026-10-16 01:29:40.974|-4 10|legacy-UTC|2026-10-15 20:39:40.970 -05:00|2026-10-16 01:39:40.974|-4 11|after-CST|2026-10-15 20:49:46.557 -05:00|2026-10-16 01:49:46.558|-1 12|after-CST|2026-10-15 20:49:47.570 -05:00|2026-10-16 01:49:47.572|-2 13|after-CST|2026-10-15 20:49:48.577 -05:00|2026-10-16 01:49:48.579|-2 14|after-CST|2026-10-15 20:49:49.590 -05:00|2026-10-16 01:49:49.591|-1 15|after-CST|2026-10-15 20:49:50.603 -05:00|2026-10-16 01:49:50.606|-3 rows_not_matching_truth|total_rows -----------------------|---------- 0|15 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'; --- 13.6 The hard part in real life: without a Phase flag, which rows are UTC and which are local? Heuristic attempt: rows with OrderID > last legacy id. Works here only because we know the cut-over id. last_legacy_id -------------- 10 # sqlcmd exit code: 0