Parentheses!

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 @LocalEnd

Hopefully 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. =)

True story.

Author: natethedba

I'm a SQL Server DBA, family man, and all-around computer geek.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s