The Cloud Time

Question

Post reply

The Cloud Time

Steve Jones - SSC Editor

SSC Guru

Points: 736172
More actions
March 31, 2020 at 12:00 am

#3733830

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

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

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

akabir SSC Enthusiast Points: 120 More actions · Answer 1

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: 5497 More actions · Answer 2

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 SSCarpal Tunnel Points: 4183 More actions · Answer 3

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: 2801 More actions · Answer 4

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

Tom Uellner SSCrazy Points: 2995 More actions · Answer 5

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: 736172 More actions · Answer 6

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: 72624 More actions · Answer 7

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”