In the last post, I showed you how to set timezone in Windows Azure. In this post, I would like to show how to manage timezone in SQL Azure.

Current Limitation

At the time this post is written, unfortunately there’s no to change SQL Azure timezone.

http://social.technet.microsoft.com/wiki/contents/articles/sql-azure-faq.aspx

Selecting Default UTC Timezone VS Local Timezone

Likewise in Windows Azure VM, SQL Azure use UTC as default timezone, regardless which datacenter you choose. Meaning that when you call getdate() function, it’s going to return you the current date and time according to the server’s timezone.

1. Use Default UTC TImezone

If you are building a new system with new created database without any existing data, I would consider UTC default timezone is fine.You can store the time as UTC inside your database. Optionally, if you want to ensure your local timezone to be displayed in UI, you can actually control it at the presentation layer.

2. Use Local Timezone

However, imagine you have existing application with bunch of data (including date time column) using your own local timezone, it will definitely messed up when you move to SQL Azure UTC timezone. The data inconsistency will definitely occur. Then you’ll need to determine either:

a. to convert your current data to UTC-timezone compliance OR

b. change your any SQL Objects (including functions, stored procedure, etc.) to match your local date time.

It’s a Tricks / Workaround, Not Actually a Solution

I’ve tried to look for various solution on the internet, mailing list, or even talk with Microsoft MVPs and Product Group folks but still there’s no single answer that could really satisfy me. Accordingly, I would call the following technique as a trick or workaround, instead of saying a solution.

Back to the second point discussed at above, converting your current data to UTC-timezone maybe very painful especially you have a lot of date and time data inside your table. In my personal opinion, I would prefer changing SQL Objects. But one very important consideration is to make it flexible and elegant enough, especially when in the future, we would need to convert back to the original state.

Well, you could definitely Find and Replace any objects inside SQL schema from getdate() to select dateadd(hh,8,getdate()). But of course it’s not going to be a nice way. As such, here’s what I’ve done to make it better.

1. Create a User-Defined Function: GetLocalDate()

CREATE FUNCTION [dbo].[GetLocalDate]

(

    @TimezoneDiffInHour TINYINT = 8

    -- default set to 8 (GMT +8 = Singapore Timezone) 

)

RETURNS DATETIME

AS

BEGIN

    RETURN DATEADD(Hh, @TimezoneDiffInHour , GETUTCDATE())

END

To call this function, you can either use:

SELECT dbo.GetLocalDate(DEFAULT)

-- OR

SELECT dbo.GetLocalDate(8)

-- 8 denotes Singapore Timezone

2. Script Out The Entire Database Schema

You would need to find and replace entire SQL objects to identify which of the object that use getdate() function. Before doing so, you’ll of course need to generate the entire database schema. You can do so using the generate script wizard in SQL Server Management Studio.

3. Find and Replace getdate() with dbo.GetLocalDate(DEFAULT)

The next step is to find which the database object that use getdate() function. Change it to dbo.GetLocalDate(DEFAULT). Execute it by altering the existing object.

By doing so, I can easily switch to UTC timezone system, just by only alter my GetLocaDate() function with Default value 0 on @TImezoneDiffInHour parameter. I don’t have to regenerate and convert back my objects again.

Frankly speaking, it may not the the best solution, but it just serves as workaround and fits my need. I won’t be surprise if there’s a better solution in the future, hopefully we can natively change the timezone in SQL Azure.