The Cloud Time

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

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

  • 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

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

     

  • 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

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

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

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

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