The Cloud Time

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719096

    Comments posted to this topic are about the item The Cloud Time

  • akabir

    SSC Enthusiast

    Points: 114

    If I check time at Denver on March 30, will I find March 12? (2020-03-12 22:34 -07:00)

  • Bulent Gucuk

    SSCertifiable

    Points: 5023

    I happened to have an Azure SQL Database in the same region and when I executed the following statement I get the following result set.

    SELECT GETDATE() AT TIME ZONE 'US Mountain Standard Time', GETDATE()

    2020-03-31 13:35:05.147 -07:00     2020-03-31 13:35:05.150

  • Robert Eder

    Hall of Fame

    Points: 3405

    GETDATE() returns the datetime of the server executed against.  The AT TIME ZONE clause tells SQL that the datetime returned from GETDATE() is at the time zone US Mountain Standard Time.  As the question states, the correct answer is "2020-03-30 16:34 -07:00"  However, GETUTCDATE() returns UTC datetime.  With the AT TIME ZONE clause, this datetime is returned "2020-03-30 22:34 -07:00", which is the incorrect time.

    See the code block below.  If you execute, you will note the AtTimeZone column will have the same time value as the datetime column.

    SELECT *
    FROM (VALUES
    ('GETDATE()', GETDATE(), GETDATE() AT TIME ZONE 'US Mountain Standard Time')
    , ('GETUTCDATE()', GETUTCDATE(), GETUTCDATE() AT TIME ZONE 'US Mountain Standard Time')
    ) AS t([Function], [DateTime], [AtTimeZone])

    If you had GETUTCDATE() AT TIME ZONE UTC AT TIME ZONE "US Mountain Standard Time", you would receive the correct time.

     

  • Bobby Russell

    SSCrazy

    Points: 2641

    How is the correct answer correct? Am I missing something? The correct answer is item #1 and it's being marked incorrect. ???

    When I run the query on my Azure server in that zone I get the first answer (time adjusted obviously).

    #confusedaf

  • TUellner

    SSCrazy

    Points: 2561

    Have to agree with everyone else here. GETDATE() will return the current date and time for the server, GETUTCDATE() will return the date and time that is marked as the correct answer. For GETDATE() the correct answer should be 1.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719096

    OK, this is a blown question. I meant to specify Azure SQL Database and did so in the tag, but not the question. I have altered that to be clear.

    In Azure SQL Database, the location is irrelevant. The time is UTC. I have a US West db and at 12:26pm, I ran this.

    2020-03-31 12_28_32-SQLQuery2.sql - dkranchus.database.windows.net.master (sjones (95))_ - Microsoft

    The question was for the PaaS service, not the IaaS one, so my apologies and my mistake.

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71756

    Interesting. i don't usually work across time zones, so found this discussion illuminating

    thanks for this.

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

Viewing 8 posts - 1 through 8 (of 8 total)

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