# 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".

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

• 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?

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.)

• This reply was modified 3 years, 6 months ago by  Marcia J.
• 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_clock21/05/2019 17:00:00London_5pmDenver_5pm21/05/2019 17:00:00 +01:0021/05/2019 17:00:00 -06:00`

Thanks, 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)