The Datatimeoffset Value

Question

The Datatimeoffset Value

Steve Jones - SSC Editor

SSC Guru

Points: 736428
More actions
May 21, 2019 at 12:00 am

#3632150

Comments posted to this topic are about the item The Datatimeoffset Value

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 1

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)

Stewart "Arturius" Campbell SSC Guru Points: 72634 More actions · Answer 2

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”

Toreador SSChampion Points: 11368 More actions · Answer 3

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?

Thom A SSC Guru Points: 99133 More actions · Answer 4

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

ildjarn.is.dead Ten Centuries Points: 1165 More actions · Answer 5

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.

Tom_Hogan Hall of Fame Points: 3903 More actions · Answer 6

Got it wrong but the explanation was excellent.

Thanks Steve.

Steve Jones - SSC Editor SSC Guru Points: 736428 More actions · Answer 7

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.

Ray Herring SSCertifiable Points: 5533 More actions · Answer 8

Ray Herring

SSCertifiable

Points: 5533

May 21, 2019 at 2:56 pm

#3645744

I HATE DST !

We should all just use UTC 🙂

Marcia J SSCertifiable Points: 5649 More actions · Answer 9

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 5 years, 7 months ago by Marcia J.

George Vobr SSChampion Points: 10007 More actions · Answer 10

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:00

Thanks, for this interesting question.

Shayn Thomas SSCertifiable Points: 5863 More actions · Answer 11

DATETIMEOFFSET always gets me, and has again,

thanks for the question

---------------------------------------------------------------------------------------
The more you know, the more you know that you dont know