Short post. The lesson is: parenthesis placement MATTERS! A lot.
The question is simply this. Given a day (like today, which at the time I drated this was 12/18/2020; yes, I understand it’s now 12/20, you’ll see why in a bit)… Anyway, given a day, which starts at midnight and ends at midnight the next day, in local server time (which, in my case, is PST — yeah, don’t judge me, I can’t change it, and I’ve already screamed into the void about it), tell me what starting and ending datetimes that corresponds to in another time zone — in this case, China.
DECLARE @AsOfDate datetime = '12/18/2020';
DECLARE @LessThanDate datetime = DATEADD(DAY, 1, @AsOfDate); --Ends the following midnight
DECLARE @LocalStart datetime, @LocalEnd datetime; --For storing the converted values (in the other time zone)
DECLARE @TzName varchar(50); --You could even look this up from a table!
PRINT 'Start/end:'
PRINT @AsOfDate
PRINT @LessThanDate
PRINT ''
PRINT 'local TZ name:'
SELECT @TzName = 'China Standard Time'
PRINT @TzName
SET @LocalStart = CONVERT(datetime, CONVERT(datetimeoffset, @AsOfDate ) AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE @TzName);
SET @LocalEnd = CONVERT(datetime, CONVERT(datetimeoffset, @LessThanDate) AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE @TzName);
PRINT ''
PRINT ('to PST, to local, INCORRECT due to misplaced parens:')
PRINT @LocalStart
PRINT @LocalEnd
SET @LocalStart = CONVERT(datetime, CONVERT(datetimeoffset, @AsOfDate AT TIME ZONE 'Pacific Standard Time') AT TIME ZONE @TzName);
SET @LocalEnd = CONVERT(datetime, CONVERT(datetimeoffset, @LessThanDate AT TIME ZONE 'Pacific Standard Time') AT TIME ZONE @TzName);
PRINT ''
PRINT ('to PST, to local, CORRECT!')
PRINT @LocalStart
PRINT @LocalEndHopefully this is a nice quick reminder that you can (somewhat) easily use the ‘AT TIME ZONE’ operator in TSQL to help you navigate the treacherous waters of dealing with time zones.
I literally spent hours beating my fists against the keyboard using variations on the INCORRECT methodology (with the parens in the wrong place) — do I have to reverse it and put @TzName first, then PST? Am I starting in the wrong place? WHY IS THIS SO HARD!?!? Then, like you should always do in such situations (and preferably much sooner than hours into it), I walked away. Came back to it on a Sunday afternoon, and BAM.
In fact, savvy SQL-ers will note that I don’t even need the internal CONVERT — and by omitting that, I can eliminate the whole source of my confusion in the first place (those darn parens!)–
DECLARE @AsOfDate datetime = '12/18/2020', @LocationID int = 4;
DECLARE @LessThanDate datetime = DATEADD(DAY, 1, @AsOfDate);
DECLARE @LocalStart datetime, @LocalEnd datetime;
DECLARE @TzName varchar(50);
PRINT 'Start/end:'
PRINT @AsOfDate
PRINT @LessThanDate
PRINT ''
PRINT 'local TZ name:'
SELECT @TzName = TimezoneName
FROM ReportRS.dbo.LocationTzOffset
WHERE LocationID = @LocationID;
PRINT @TzName
SET @LocalStart = CONVERT(datetime, @AsOfDate AT TIME ZONE @TzName AT TIME ZONE 'Pacific Standard Time');
SET @LocalEnd = CONVERT(datetime, @LessThanDate AT TIME ZONE @TzName AT TIME ZONE 'Pacific Standard Time');
PRINT ''
PRINT ('from local to PST:')
PRINT @LocalStart
PRINT @LocalEnd
SET @LocalStart = CONVERT(datetime, @AsOfDate AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE @TzName);
SET @LocalEnd = CONVERT(datetime, @LessThanDate AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE @TzName);
PRINT ''
PRINT ('from PST to local:')
PRINT @LocalStart
PRINT @LocalEnd
So, second lesson: When you’re stuck on a problem, take a breath and walk away. Come back to it later. It’ll still be there.
Happy holidays, stay safe. =)
nate_the_dba 