Windows Azure SQL Database, GETDATE, SYSDATETIME, SYSUTCDATETIME and Rounding

When I’ve been putting data into Windows Azure SQL Database (WASD) in the past, I’d normally been providing the dates from my own system. This week, I had the first time where I wanted to put a column default that provided a date in WASD. It suddenly dawned on me that I wasn’t sure what timezone the date would be from. As I was using the Southeast Asia data centre (in Singapore), I was presuming the value would be based on Singapore’s timezone. So it was time to find out.

Prior to SQL Server 2008, GETDATE() was the normal way that we’d retrieve the current time from the server. GETDATE() returns a datetime data type. SQL Server 2008 introduced the datetime2 data type (a higher-precision data type with a poorly-chosen name). SQL Server then provided SYSDATETIME() as a replacement for GETDATE(). SYSDATETIME() returns the datetime2 data type. To make it easier to work with UTC-based values, SQL Server also provides SYSUTCDATETIME().

If I connect just now to my database, and execute the query:

SELECT GETDATE() AS GetDateValue,        SYSDATETIME() AS SysDateTimeValue,        SYSUTCDATETIME() AS SysUTCDateTimeValue;

The values returned were:

GetDateValue            SysDateTimeValue            SysUTCDateTimeValue
-———————- ————————— ————————— 2013-01-19 22:23:21.830 2013-01-19 22:23:21.8400294 2013-01-19 22:23:21.8400294

(1 row(s) affected)

I was pleasantly surprised to see that the values were UTC based instead. That’s really useful as it means that no matter which Azure data centre you connect to, they all have the same concept of “current time”.

What also surprised me is that while the SYSDATETIME() and SYSUTCDATETIME() values were identical, the GETDATE() value wasn’t just a rounded version of the same time. It was an earlier time so it must be resolved separately in the query. That’s not an Azure-specific issue though. If I execute the same query against my laptop system, the following output is produced:

GetDateValue            SysDateTimeValue            SysUTCDateTimeValue
-———————- ————————— ————————— 2013-01-20 09:27:41.503 2013-01-20 09:27:41.5051908 2013-01-19 22:27:41.5051908

(1 row(s) affected)

Note that my system is operating in +11 timezone so my SYSDATETIME() value is 11 hours ahead of my SYSUTCDATETIME() value. But again notice that it’s exactly the same value when allowing for those 11 hours. However, the GETDATE() value is different again. 

So there are two messages from this:

• Azure SQL Databases are always UTC timezone based (which is good news)
• Don’t depend upon GETDATE() and SYSDATETIME() returning exactly the same time in a single query, after allowing for rounding.

2013-01-20