SQL Azure DATETIME Functions

Microsoft SQL has always included a number of date and time functions.  These results of these functions were always based on the operating system for the machine the SQL server was running on.  But what results will you get when you’re dealing with SQL Azure?

The server(s) are all virtual.

They are all based on UTC, Coordinated Universal Time.  Yes, I know the acronym should be CUT.  Just go to Wikipedia if you want to try and make sense of this.

Since all these functions will run based on UTC, you’ll always get the same results, no matter which data center you choose to be your primary.

The table below shows the results from the following query run against a SQL Azure instance hosted in North Europe on 20th June 2011 07:08:53 UTC.

SELECT
    SYSDATETIME() as SYSDATETIME,
    SYSDATETIMEOFFSET() as SYSDATETIMEOFFSET,
    SYSUTCDATETIME() as SYSUTCDATETIME,
    CURRENT_TIMESTAMP as currenttimestamp,
    GETDATE() as getdate,
    GETUTCDATE() as getUTCdate;

Query
SYSDATETIME() 2011-06-20 07:08:53.1026073
SYSDATETIMEOFFSET() 2011-06-20 07:08:53.1026073 +00:00
SYSUTCDATETIME() 2011-06-20 07:08:53.1026073
CURRENT_TIMESTAMP 2011-06-20 07:08:53.113
GETDATE() 2011-06-20 07:08:53.113
GETUTCDATE() 2011-06-20 07:08:53.100

If you are like me, whenever you deal with international users, you already changed your servers to UTC, so taking this into consideration for SQL Azure should be nothing new.  If you’re not used to this.  Perhaps now is the time to consider the benefits to having your server use UTC, store UTC + offsets in your tables, and allow users to run with multiple time zones.

It sure makes scheduling easier that way.  Or at least it beats trying to figure out what time to hold a meeting between London the US and Mexico, both before and after Daylight Savings time kicks in!