The Datatimeoffset Value

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714389

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

  • Jeff Moden

    SSC Guru

    Points: 993790

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

    Helpful Links:
    How to post code problems

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71215

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

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

    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.

  • ildjarn.is.dead

    SSChasing Mays

    Points: 653

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

    Got it wrong but the explanation was excellent.

    Thanks Steve.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714389

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

    I HATE DST !

    We should all just use UTC πŸ™‚

  • Marcia J

    SSCertifiable

    Points: 5493

    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 1 month ago by  Marcia J.
  • George Vobr

    SSCrazy Eights

    Points: 8881

    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_5pm Denver_5pm
    21/05/2019 17:00:00 +01:00 21/05/2019 17:00:00 -06:00

    Thanks, for this interesting question.

  • Shayn Thomas

    SSCertifiable

    Points: 5363

    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 12 (of 12 total)

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