September 5, 2016 at 11:14 pm
Comments posted to this topic are about the item AT TIME ZONE
September 5, 2016 at 11:21 pm
This was removed by the editor as SPAM
September 6, 2016 at 1:19 am
Interesting, thanks!
But, how do I convert it in previous release of sqlserver?
September 6, 2016 at 2:40 am
Got it wrong--read the examples in the AT TIME ZONE article and noticed they were returning the same time, but a different datetime offset, so assumed the same would happen in this case. Didn't realise the examples were talking about a conversion within the same time zone, and have never used DATETIMEOFFSET, so it seemed reasonable to me that it would work by storing the UTC time and an offset, rather than the *local* time and the offset. :crying:
September 6, 2016 at 6:32 am
Yeah, I got it wrong too. But I learned something new, so thank you.
September 6, 2016 at 7:41 am
What could have thrown me was that I though ET was 5 hours off GMT. ET is five hours behind time in England and 6 in central Europe. I just looked at a time zone map and counted five zones from England. Where did the four come from?
September 6, 2016 at 7:46 am
RonKyle (9/6/2016)
What could have thrown me was that I though ET was 5 hours off GMT. ET is five hours behind time in England and 6 in central Europe. I just looked at a time zone map and counted five zones from England. Where did the four come from?
Daylight Savings, presumably? EDT is UTC - 4.
September 6, 2016 at 7:54 am
Daylight Savings, presumably? EDT is UTC - 4.
That could be the answer, which means SQL is smart enough to know when to apply daylight savings. The question does ask for Eastern Standard to be returned, however.
I'm not doubting the accuracy of the question, though I haven't tried it yet. I'm just a little confused at the math.
September 6, 2016 at 8:45 am
RonKyle (9/6/2016)
What could have thrown me was that I though ET was 5 hours off GMT. ET is five hours behind time in England and 6 in central Europe. I just looked at a time zone map and counted five zones from England. Where did the four come from?
+1
I'm working on the Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)
Jun 17 2016 19:14:09
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 10586: )
In the registry hive: KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones
is for the TIME ZONE 'Eastern Standard Time' stored the value UTC-05:00.
Anyway, it's an interesting question, thanks Steve.
September 7, 2016 at 2:39 am
Good Question. Thanks. Learned something useful here.
September 7, 2016 at 6:52 am
Great question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 7, 2016 at 6:54 am
I look forward to SQL Server 2016.
September 7, 2016 at 9:07 am
Cool function. Thanks for the question Steve!
September 7, 2016 at 9:32 am
RonKyle (9/6/2016)
What could have thrown me was that I though ET was 5 hours off GMT. ET is five hours behind time in England and 6 in central Europe. I just looked at a time zone map and counted five zones from England. Where did the four come from?
This threw me too.
September 12, 2016 at 9:58 am
I conflated a couple things here, and somewhat caused an issue.
The East Coast of the US is 4 or 5 hours off from the UK, depending on the time of year. The countries switch a different dates of the spring and fall.
That being said, EST is technically invalid for July. The US East Coast is on EDT, not EST then. So the time would be 5. However, the EST value still can be used, even though the UK is on daylight time.
Strange, and my apologies. I should have chosen a different 0 time.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply