AT TIME ZONE – SQL Server 2016 – My new favourite

Hi Friends,

Back to you with one of my favourite feature introduced with SQL Server 2016.

Working over a decade with SQL Server in heterogeneous line businesses, heterogeneous market, unlike customers from geographic and supporting various timezones. At many occasions found messy myself while playing nicely with different timezones. Specially when day light saving switch is ON/OFF. Definitely it’s ease to get to know with my machine what time zone customers are at present, however equally difficult to tell what time zone my customers will be in at other times of the year. Oh God! failing to cope up.

SQL Server 2016 has fixed this totally! 🙂

Simply use “AT TIME ZONE” clause against any date time value & you’re able to determine what time will be into particular time zone. Wow! what a relief. I’m going to explain it bit more with help of a quick example.

I’m currently based in India and following IST timezone. Let’s see how this new clause exhibits result.

SELECT CONVERT(datetime,'20160601 00:00') 
    AT TIME ZONE 'India Standard Time';
-- Result - 2016-06-01 00:00:00.000 +05:30

Thought results is pretty much same however just added +5:30 (as mostly database servers run in UTC timezone).

Below example will help you to determine how same date time of India will be EST (US Timezone).

SELECT CONVERT(datetime,'20160601 00:00') 
	AT TIME ZONE 'India Standard Time'
	AT TIME ZONE 'US Eastern Standard Time';
-- Result - 2016-05-31 14:30:00.000 -04:00

Apparently quite easy to convert anytime zone to anytime zone with this new clause. It becomes extremely useful. This feature definitely ease out quite a bit of my pain for sure.

Note: Different time zones are listed in sys.time_zone_info, along with what the current offset is & daylight saving details.

Happy Learning!

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Avanish Panchal on Twitter | Follow Avanish Panchal on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Avanish Panchal

Avanish carries around 15 years of experience in IT industry. He is post graduate in Computer Science, followed by Masters in Business Administration. He has worked on multiple technologies like SQL Server, .net & Sybase with world largest bank(s)/IT consulting firm(s) like JPMorganChase, CapGemini, Datamatics etc. Currently holds position of Database Architect in BioPharma Global Leader. His area of focus are SQL Server DB Engine, Security, Storage, Virtualization & Infrastructure Consolidation. Passionate for delivering Database IT Infrastructure to satisfy and exceed the needs of the enterprise. You may find him active on various forums like SQLBangalore, SQLServerGeeks & many more. His SQL Server passion helped him to be the Core Team member of Asia's First SQL Server Conference in 2015 @ Bangalore (#SSGAS2015).

View all posts by Avanish Panchal →

Leave a Reply

Your email address will not be published. Required fields are marked *