SQL SERVER – Simple Explanation of AT TIME ZONE Feature

Recently I received a simple question about AT TIME ZONE feature. This is the new feature which is introduced in SQL Server 2016 and later version of the SQL Server. As this is a relatively new feature the adoption of this feature is also a bit slow. However, let us understand how this function really works in a few simple words.

Let us assume that we are staying East Cost USA and we want our time to be converted to West Cost USA, what is the simplest way to achieve the same?

Well, here is the answer:

Step 1: Specify our current time zone to our time format.

SELECT CONVERT(datetime,GETDATE())
AT TIME ZONE 'Eastern Standard Time'

Step 2: Now, once again specify the new desired timezone to our new time and that will be our answer.

SELECT CONVERT(datetime,GETDATE()) 
AT TIME ZONE 'Eastern Standard Time'
AT TIME ZONE 'Pacific Standard Time';

Let us now see the entire script in action.

SELECT	CONVERT(datetime,GETDATE()) CurrentTime,
		CONVERT(datetime,GETDATE()) 
		AT TIME ZONE 'Eastern Standard Time' EST,
		CONVERT(datetime,GETDATE()) 
		AT TIME ZONE 'Eastern Standard Time'
		AT TIME ZONE 'Pacific Standard Time' PST;

You can clearly see from this example how we can now convert one time zone to another time zone and there is no confusion at all about time and its timezone due to availability of the offset next to it.

If you want to see all the available time zone you can run the following SQL Script and it will return all the time zone available inside SQL Server.

SELECT * FROM sys.time_zone_info

Reference: Pinal Dave (https://blog.sqlauthority.com)