none
checking my understanding of datetimeoffset

    Question

  • I'm on SQL Server 2012 in Eastern Standard Time, which I believe is -4 hours from UTC time. I mention this because I'm thinking this is relevant for understanding datetimeoffset.

    When I look in a certain column defined as DATETIMEOFFSET(4) and I see a value like this,

    '2013-10-26 10:01:53.3114582 -04:00'

    I understand this to mean, date and time of 2013-10-26 10:01:53.3114582, -4 hours behind UTC. Am I correct? So, if I add 4 hours to 2013-10-26 10:01:53.3114582, I get to UTC.

    (By the way, how does my SQL Server know that it is in Eastern Standard Time? Was it a setting when the server was setup?)

    Follow up question. Why is it when I do this

    DECLARE @Now DATETIMEOFFSET(4)
    SET @Now = GETDATE()
    SELECT @Now

    I get this back? '2013-10-26 11:53:04.2170 +00:00'

    ''2013-10-26 11:53:04.2170' happens to be my local time. Shouldn't it say ''2013-10-26 11:53:04.2170 -04:00'?

    Saturday, October 26, 2013 4:03 PM

Answers

  • I suspecct you are on Eastern Daylight Time (which is UTC - 4 hours).  Eastern Standard Time is UTC - 5 hours.

    Yes, '2013-10-26 10:01:53.3114582 -04:00' means the time is 10:01 and that is 4 hours behind UCT, so the UTC time would be 14:01.

    GETDATE() does not understand datetime offset.  If you want the datetime offset value, use SYSDATETIMEOFFSET().

    DECLARE @Now DATETIMEOFFSET(4)
    SET @Now = GETDATE()
    SELECT @Now
    
    DECLARE @NowOffset DATETIMEOFFSET(4)
    SET @NowOffset = SYSDATETIMEOFFSET()
    SELECT @NowOffset

    And SQL Server knows what timezone you are in by getting it from Windows.  As part of the Windows Date and Time settings, you specify what time zone you are in and whether or not your location uses daylight savings time.

    Tom

    Saturday, October 26, 2013 4:55 PM

All replies

  • I suspecct you are on Eastern Daylight Time (which is UTC - 4 hours).  Eastern Standard Time is UTC - 5 hours.

    Yes, '2013-10-26 10:01:53.3114582 -04:00' means the time is 10:01 and that is 4 hours behind UCT, so the UTC time would be 14:01.

    GETDATE() does not understand datetime offset.  If you want the datetime offset value, use SYSDATETIMEOFFSET().

    DECLARE @Now DATETIMEOFFSET(4)
    SET @Now = GETDATE()
    SELECT @Now
    
    DECLARE @NowOffset DATETIMEOFFSET(4)
    SET @NowOffset = SYSDATETIMEOFFSET()
    SELECT @NowOffset

    And SQL Server knows what timezone you are in by getting it from Windows.  As part of the Windows Date and Time settings, you specify what time zone you are in and whether or not your location uses daylight savings time.

    Tom

    Saturday, October 26, 2013 4:55 PM
  • Yes, I was probably mistaken on my exact time zone. But your reply helped me understand. Thanks.
    Saturday, October 26, 2013 5:05 PM