Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Demo script for AT TIME ZONE usage with reporting on a transaction table
/* 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
@NJohnson9402

This comment has been minimized.

Copy link
Owner Author

@NJohnson9402 NJohnson9402 commented Oct 31, 2019

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.