One of the new items in SQL Server 2016 is the super awesome time_zone_info table. When I heard about it, I started to think about all the cool things that it could help me do. First, let’s look at the table.
SELECT * FROM [sys].[time_zone_info]
Yes, it is 132 rows of magic! Now that we have this super cool table, how do we use it? Let’s pretend that my data is time-stamped in US Mountain Standard Time, but I want to display it in Western Australia Standard Time. I would do it like this:
SELECT GETDATE() AS GETDATE_Time, GETDATE() AT TIME ZONE 'US Mountain Standard Time' AS Mountain_Time, GETDATE() AT TIME ZONE 'US Mountain Standard Time' AT TIME ZONE 'W. Australia Standard Time'AS W_Aus_Time;
I am including the GetDate column so you can see that GetDate is using my time zone, but I have to tell it what time zone it is before I can convert it to another one.
Caution: If I put in the Hawaiian time zone instead of Mountain time on the GetDate, SQL won’t correct me, it will just do the math like a good little system and assume I know what time zone I am using to start.
I am am really loving the new features in SQL 2016! I hope you are enjoying it too!
This posts song is Cleopatra by The Lumineers
Hello, I attended your very informative tips and tricks presentation on SQL Saturday. Where can I find that presentation? Thank you!
Hi, you can find it on SlideShare. Here is the link: https://www.slideshare.net/AndreaAllred1/sql-server-management-studio-tips-and-tricks?qid=1cd0c63f-b064-4980-a40b-e117b447eb67&v=&b=&from_search=1