Skip to content
September 13, 2011 / thewiseguy99

SQL Azure and the DateTime.Now issues

The SQL databases on the Azure cloud are pegged against Greenwich Mean Time or Coordinated Universal Time however many applications are using DateTime.Now which is the time according to the regional settings specified on the host machine. Sometimes this is not an issue when the DateTime is not used for any time spanning or comparisons and instead for display only. However if you migrate an existing Database to SQL Azure using the dates populated via GETDATE() or DateTime.Now you will have an offset, in my case it’s 8 hours.

Here’s a layout of our test environment when this problem became apparent,

  • we have our application & services locally in our test environment
  • we have our testing databases on Azure

Now the problem is that all of this at one point was very traditional in that it was hosted off-site on our servers with the clock/regional settings configured the same and using NTP to reduce drift. So far so good. The users would log into the system using their WPF application,  or our web application. The users could create products in the application which then get persisted via the web services and saved on the database.

The problem exposed itself during some initial tests after migrating our test databases onto Azure yet using our local test environment and then running our initial selenium tests. We found when a log-in was performed the expiry logic was set on the servers using DateTime.Now and persisted to the SQL Azure database. When the second request came in and a verification on the security token was performed it expired almost 8 hours ago. Eureka!

It is embarrassing how something so obvious is overlooked, I really should have known better but it was easy enough to fix.

You will need to replace the date function in SQL Azure to use

getdate() -> getutcdate()

Although on SQL Azure these will be one in the same they will not behave the same when/if you synchronize this database to a SQL Server instance. When a datetime is required to define expiration or updates then replace the DateTime.Now in your .NET applications

myOrder.TimePlaced = DateTime.Now;
myOrder.Expires = DateTime.Now.AddDays(2);

to

myOrder.TimePlaced = DateTime.UtcNow;
myOrder.Expires = DateTime.UtcNow.AddDays(2);

This way no matter what environment the application is hosted in it will always use the same times to key against. To display the UTC date to a user based on the server’s regional settings you can use the ToLocalTime() method on the DateTime.

TextBox1.Text = myOrder.TimePlaced.ToLocalTime();

Although the DateTime was UTC it will now be converted to the timezone of the server. If your application is hosted in Windows Azure then this would be converted to UTC.

Finally if you want to display the DateTime from UTC to the users time you can do this in Javascript.

script type="text/javascript">
      document.write(new Date(<%= myOrder.TimePlaced.ToString("r") %>));
</script>
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: