This week I attended another great webinar by Kendra Little of SQLWorkbooks.com. (If you aren't familiar with Kendra, check out her free webinars and her excellent catalog of online courses.)
One neat thing that Kendra always seems to do in her webinars and courses is subtly use new(er) SQL Server features. This week, she just happened to use the AT TIME ZONE statement in one of her queries. As soon as I saw it, I knew I had to try it with Dynamics GP.
Dynamics GP doesn't have much time zone sensitive data, but one field that I am starting to rely on more frequently is the DEX_ROW_TS field, which is now present in several key GP tables. This field stores a last updated date time stamp.
DEX_ROW_TS is a bit unique for GP for at least two reasons. First, it's a rare time stamp field. While GP has many date fields, those date fields normally have a time of 00:00:00.000--so it's just a date at midnight, with no timestamp.
The second unique thing about DEX_ROW_TS is that it stores the datetime with a UTC timezone offset. So if you ever query the DEX_ROW_TS field you need to remember that it isn't local time.
I previously wrote a post about DEX_ROW_TS and how to use some SQL date functions to convert the value to your local time zone, but that approach felt a bit like duct tape and twine, and I would have to look up the syntax every time to use it.
Enter the very cool SQL Server 2016 AT TIME ZONE function. This function makes it very easy to assign a time zone to a datetime value, and then convert it to another time zone.
(I'm calling AT TIME ZONE a function for now because I haven't found a better name for it. It doesn't read like a typical function, but it acts like one, so Function is the best name I have so far. If you know of the proper technical name for it, let me know in the comments below.)
Here is an example where I convert DEX_ROW_TS to Pacific Local Time.
The query first 'assigns' the DEX_ROW_TS value to the UTC time zone:
DEX_ROW_TS
AT TIME ZONE 'UTC'
And then it adds another time zone to convert that UTC value to Pacific time:
DEX_ROW_TS
AT TIME ZONE 'UTC'
AT TIME ZONE 'Pacific Standard Time'
In the results, you see that DEX_ROW_TS shows that I updated AARONFIT0001 on Feb 24 at 1:03pm. But that is UTC time. After converting to Pacific Standard Time, we see that the update was made on Feb 23 at 5:03am local time.
The function "AT TIME ZONE" is a bit clunky looking, and as I mentioned, the syntax is nothing like a typical function, but it's very easy to remember and easy to use. The only thing you may need to research are the valid time zone names.
Unfortunately, this feature was introduced with SQL Server 2016, so you will need to be using 2016 or higher to take advantage of it. But hopefully as customers upgrade GP, they upgrade their SQL Server version so that you can take advantage of this cool feature.
Here is another post discussing AT TIME ZONE:
https://sqlperformance.com/2016/07/sql-plan/at-time-zone
Enjoy!
Steve Endow is a Microsoft MVP in
Los Angeles. He is the owner of Precipio Services, which provides
Dynamics GP integrations, customizations, and automation solutions.
No comments:
Post a Comment