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
