The Datatimeoffset Value
-
Comments posted to this topic are about the item The Datatimeoffset Value
-
Ya know... this finally eliminated the confusion I had with this function (mostly because I don't use it but might have better reason to now). Thanks Steve.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Interesting question, thanks Steve
I seldom have had the need to use this function, so learned something...
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex” -
Ignoring the fact that the dates are a week apart
I did a search before replying and that told me that Colorado is also on daylight savings time in May, so 7 zones away from London would still be -7. Is this not the case?
- Toreador wrote:
Ignoring the fact that the dates are a week apart
I did a search before replying and that told me that Colorado is also on daylight savings time in May, so 7 zones away from London would still be -7. Is this not the case?No because in May, as the answer explains, London is in Day Light Saving Time (BST), which is UTC + 1 and hence the time
2019-05-15 17:00:00 +01:00. As Colorado is 7 times zones away from London, you have 1 - 7 = -6 =2019-05-15 10:00:00 -06:00.The UK is not GMT (which is UTC + 0) during the Summer months.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
I always thought the format was 'UTC time +-Offset'.
London 17:00:00 in May is '2019-05-15 16:00:00 +01:00'. Colorado at the same time is '2019-05-15 16:00:00 -06:00'.
The whole purpose of datetimeoffset is that everything is UTC-based, instead of local time based, the local time is indicated as the timezone difference.
-
Got it wrong but the explanation was excellent.
Thanks Steve.
-
Glad I finally got something right :). Writing these questions clearly and getting answers organized is harder than I ever expected.
It took me some time to understand this function, though I still often have to refer to docs.
-
I HATE DST !
We should all just use UTC
-
Same.
(This was meant to be in reply to TOM_HOGAN who said "Got it wrong but the explanation was excellent." But I hit the wrong reply button.)
-
Datetimeoffset defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock.
If you would like to represent the same time 5:00 in the afternoon in Colorado in May, in a datetimeoffset variable, you can do it this way:
DECLARE @t DATETIME = '2019-05-21 17:00:00';
DECLARE @London DATETIMEOFFSET;
DECLARE @Colorado DATETIMEOFFSET;
SELECT @t AS InputDate_5pm_24h_clock;
SELECT @London = @t AT TIME ZONE 'GMT Standard Time';
SELECT @Colorado = @t AT TIME ZONE 'Mountain Standard Time';
SELECT @London AS London_5pm, @Colorado AS Denver_5pm;
-- Results --
InputDate_5pm_24h_clock
21/05/2019 17:00:00
London_5pmDenver_5pm
21/05/2019 17:00:00 +01:0021/05/2019 17:00:00 -06:00Thanks, for this interesting question.
-
DATETIMEOFFSET always gets me, and has again,
thanks for the question
---------------------------------------------------------------------------------------
The more you know, the more you know that you dont know
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply