Demo script for AT TIME ZONE usage with reporting on a transaction table

AtTimeZoneDemo.sql

/* The system table that contains TZ info
SELECT tzi.name, tzi.current_utc_offset, tzi.is_currently_dst
FROM master.sys.time_zone_info tzi
*/

--Drop & create tables
IF (OBJECT_ID('dbo.OfficeLocation') IS NOT NULL)
	DROP TABLE dbo.OfficeLocation;
IF (OBJECT_ID('dbo.MyTrans') IS NOT NULL)
	DROP TABLE dbo.MyTrans;
GO

CREATE TABLE dbo.OfficeLocation
(
    OfficeCode varchar(2)
    , TimeZone sysname
);

CREATE TABLE dbo.MyTrans
(
    TranDate datetime
    , Description varchar(MAX)
    , TypeCode varchar(2)
    , OfficeCode varchar(2)
);

--These are the offices; HK is merely included to show that it needs to use the same TZ string as China.
INSERT INTO dbo.OfficeLocation (OfficeCode, TimeZone)
SELECT 'FR', 'Central Europe Standard Time'
UNION ALL
SELECT 'CN', 'China Standard Time'
UNION ALL
SELECT 'HK', 'China Standard Time'
;

--Note the start of each calendar day, and the start & close of business.
INSERT INTO dbo.MyTrans (TranDate, Description, TypeCode, OfficeCode)
SELECT '2019-10-31T09:00:00', 'Start of 2019-11-01 in China', 'SD', 'CN'
UNION ALL
SELECT '2019-10-31T16:00:00', 'Start of 2019-11-01 in Paris', 'SD', 'FR'
UNION ALL
SELECT '2019-10-30T09:00:00', 'Start of 2019-10-31 in China', 'SD', 'CN'
UNION ALL
SELECT '2019-10-30T16:00:00', 'Start of 2019-10-31 in Paris', 'SD', 'FR'
UNION ALL
SELECT '2019-10-29T09:00:00', 'Start of 2019-10-30 in China', 'SD', 'CN'
UNION ALL
SELECT '2019-10-29T16:00:00', 'Start of 2019-10-30 in Paris', 'SD', 'FR'
UNION ALL
SELECT '2019-10-30T08:00:00', 'Start of business 2019-10-30 in US', 'SB', 'US'
UNION ALL
SELECT '2019-10-30T17:00:00', 'Close of business 2019-10-30 in US', 'CB', 'US'
UNION ALL
SELECT '2019-10-31T08:00:00', 'Start of business 2019-10-31 in US', 'SB', 'US'
UNION ALL
SELECT '2019-10-31T17:00:00', 'Close of business 2019-10-31 in US', 'CB', 'US'
UNION ALL
SELECT '2019-10-29T17:00:00', 'Start of business 2019-10-30 in China', 'SB', 'CN'
UNION ALL
SELECT '2019-10-30T02:00:00', 'Close of business 2019-10-30 in China', 'CB', 'CN'
UNION ALL
SELECT '2019-10-30T17:00:00', 'Start of business 2019-10-31 in China', 'SB', 'CN'
UNION ALL
SELECT '2019-10-31T02:00:00', 'Close of business 2019-10-31 in China', 'CB', 'CN'
UNION ALL
SELECT '2019-10-30T00:00:00', 'Start of business 2019-10-30 in Paris', 'SB', 'FR'
UNION ALL
SELECT '2019-10-30T09:00:00', 'Close of business 2019-10-30 in Paris', 'CB', 'FR'
UNION ALL
SELECT '2019-10-31T00:00:00', 'Start of business 2019-10-31 in Paris', 'SB', 'FR'
UNION ALL
SELECT '2019-10-31T09:00:00', 'Close of business 2019-10-31 in Paris', 'CB', 'FR'
;

--Demo a few inserts using the reverse logic -- "lunch" (12 noon) in each office
INSERT INTO dbo.MyTrans (TranDate, Description, TypeCode, OfficeCode)
SELECT CONVERT(datetime, '2019-10-30T12:00:00') AT TIME ZONE ol.TimeZone AT TIME ZONE 'Pacific Standard Time'
    , 'Lunch time 2019-10-30 in China', 'LU', 'CN'
FROM dbo.OfficeLocation ol
WHERE ol.OfficeCode = 'CN'
UNION ALL
SELECT CONVERT(datetime, '2019-10-30T12:00:00') AT TIME ZONE ol.TimeZone AT TIME ZONE 'Pacific Standard Time'
    , 'Lunch time 2019-10-30 in Paris', 'LU', 'FR'
FROM dbo.OfficeLocation ol
WHERE ol.OfficeCode = 'FR'
UNION ALL
SELECT CONVERT(datetime, '2019-10-31T12:00:00') AT TIME ZONE ol.TimeZone AT TIME ZONE 'Pacific Standard Time'
    , 'Lunch time 2019-10-31 in China', 'LU', 'CN'
FROM dbo.OfficeLocation ol
WHERE ol.OfficeCode = 'CN'
UNION ALL
SELECT CONVERT(datetime, '2019-10-31T12:00:00') AT TIME ZONE ol.TimeZone AT TIME ZONE 'Pacific Standard Time'
    , 'Lunch time 2019-10-31 in Paris', 'LU', 'FR'
FROM dbo.OfficeLocation ol
WHERE ol.OfficeCode = 'FR'
;

--Show the conversions
SELECT [PacificTime] = mt.TranDate, mt.Description, mt.TypeCode, mt.OfficeCode
    , LocalTime = CONVERT(datetime, mt.TranDate AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE ol.TimeZone)
    , LocalDay = CONVERT(date, mt.TranDate AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE ol.TimeZone)
FROM dbo.MyTrans mt
JOIN dbo.OfficeLocation ol
    ON mt.OfficeCode = ol.OfficeCode

--Show a count of events by LOCAL DAY
SELECT LocalDay = CONVERT(date, mt.TranDate AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE ol.TimeZone)
    , mt.OfficeCode, N = COUNT(1)
FROM dbo.MyTrans mt
JOIN dbo.OfficeLocation ol
    ON mt.OfficeCode = ol.OfficeCode
GROUP BY mt.OfficeCode, CONVERT(date, mt.TranDate AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE ol.TimeZone)
ORDER BY LocalDay, mt.OfficeCode

For use with https://natethedba.wordpress.com/dealing-with-time-zones-in-sql/