Convert SQL Azure GetDate() UTC Time to Local Time

I have been using SQL Azure for almost a year now. I recently found out that SQL Azure stores date/times in UTC format when generated within the database using the GetDate() function and similar built in SQL date/time functions. Also, at this time there is no way to change the timezone for a SQL Azure instance.

I have done a few SQL Azure projects but I started from scratch on them and had my applications generating the date/time so it never was a problem. Recently I had to transfer an existing project from an on-premise MS SQL 2008 database to SQL Azure. The project stores all times in the Central Time Zone since the on-premise MS SQL server was configured to use that timezone when GetDate() was called. The project is about 2 years old and has a few hundred stored procedures.

The easiest solution for me was to replace all GetDate() calls in the stored procedures with a custom date/time calculation function to keep the data consistent. Ok, so lets get to it.

Case 1: Your local time zone does not observe daylight savings time and stays consistent year round.  This is easy to handle, you can simply replace GetDate() with a DateAdd() function.

You can use this by replacing n with the difference in hours between your time and UTC.

Case 2: Your local time zone observes daylight savings such as the Central Time Zone. This requires a custom set of functions for calculating when daylight savings starts and ends, then a function for determining if it is currently daylight savings time or not and returns the corrected date/time.

These are my functions for converting the SQL Azure UTC time into the Central Time Zone.

This function calculates the beginning of Daylight Savings Time which is the second Sunday in March in my area. It uses 8:00 AM UTC for the  time because Central Time is 6 hours behind UTC time before daylight savings time.  This can easily be modified for other time zones such as Eastern Time by subtracting an hour from each DATEADD function.

This function is similar to the other one, it calculates the end of Daylight Savings Time which is the first Sunday in November for my area.  It uses 7:00AM UTC for the time because it is 2:00AM Central Daylight Savings Time.  This function can also easily be converted for other time zones by adding or subtracting hours from the DATEADD function.

This last function determines whether or not the current UTC time falls in daylight savings and returns the correct time for the Central Time Zone.  I used GETUTCDATE() instead of GETDATE() so that it will work on any MS SQL database.

From here, I simply scripted all of the stored procedures and did a find/replace to easily replace every GetDate() call with my function: dbo.GetLocalDateTime() .

I based my functions on Tim Cullen’s blog post and I would like to thank him for his work.


Donate Bitcoins
Posted Under: Database Programming SQL Azure

Author

Written by Joey Iodice

I'm a software developer focusing on ASP.net, HTML5, jQuery, & Javascript.

3 Comments

  1. Alex Edwards says:

    That’s a useful post Joey but the code could be simplified to one function and using maths to work out the weekday offset rather than the Case statements and also should be independent of server’s DateFirst setting.

    Here is what I use for British Summer Time:

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    – =============================================
    – Author: Alex Edwards
    – Create date: 2012-09-20
    – Description: Produces a BST DateTime from a UTC DateTime
    – Use dbo.GetDateBST(GETUTCDATE()) to give the server time in BST
    – =============================================
    CREATE FUNCTION dbo.GetDateBST(@UTCDateTime DATETIME) RETURNS DATETIME
    AS BEGIN
    DECLARE @y VARCHAR(4) –Year of @UTCDateTime
    DECLARE @st DATETIME –UTC DateTime at which daylight savings (+1 hour) starts (last Sunday in March at 1am)
    DECLARE @nd DATETIME –UTC DateTime at which daylight savings (+1 hour) stops (last Sunday in October at 1am)
    DECLARE @i SMALLINT
    SET @y = CONVERT(VARCHAR(4),DATEPART(YEAR,@UTCDateTime))
    SET @st = CONVERT(DATETIME, @y + ‘-04-01 01:00:00′, 120)
    SET @i = @@DATEFIRST + DATEPART(weekday,@st)
    IF @i > 8 SET @i = @i – 7
    SET @st = DATEADD(day,1 – @i,@st)
    SET @nd = CONVERT(DATETIME, @y + ‘-11-01 01:00:00′, 120)
    SET @i = @@DATEFIRST + DATEPART(weekday,@nd)
    IF @i > 8 SET @i = @i – 7
    SET @nd = DATEADD(day,1 – @i,@nd)
    IF @UTCDateTime BETWEEN @st AND @nd SET @UTCDateTime = DATEADD (hour, 1, @UTCDateTime)
    RETURN @UTCDateTime –Value is now BST despite the name
    END
    GO

    It wouldn’t take much to do a Central Time version.

  2. Alex Edwards says:

    You’re welcome, but I don’t mind doing it. Here is my version adapted for the Central Daylight Timezone from UTC:

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    – =============================================
    – Author: Alex Edwards
    – Create date: 2012-09-24
    – Description: Produces a CDT (Central Daylight Time) DateTime from a UTC DateTime
    – Use dbo.GetDateCDT(GETUTCDATE()) to give the server time in CDT
    – =============================================
    CREATE FUNCTION dbo.GetDateCDT(@UTCDateTime DATETIME) RETURNS DATETIME
    AS BEGIN
    DECLARE @y VARCHAR(4) –Year of @UTCDateTime
    DECLARE @st DATETIME –UTC DateTime at which daylight savings (+1 hour) starts (second Sunday in March at 8am)
    DECLARE @nd DATETIME –UTC DateTime at which daylight savings (+1 hour) stops (first Sunday in November at 7am)
    DECLARE @i SMALLINT
    SET @y = CONVERT(VARCHAR(4),DATEPART(YEAR,@UTCDateTime))
    SET @st = CONVERT(DATETIME, @y + ‘-03-01 08:00:00′, 120)
    SET @i = @@DATEFIRST + DATEPART(weekday,@st)
    IF @i > 8 SET @i = @i – 7
    SET @st = DATEADD(day,15 – @i,@st)
    SET @nd = CONVERT(DATETIME, @y + ‘-11-01 07:00:00′, 120)
    SET @i = @@DATEFIRST + DATEPART(weekday,@nd)
    IF @i > 8 SET @i = @i – 7
    SET @nd = DATEADD(day,8 – @i,@nd)
    IF @UTCDateTime BETWEEN @st AND @nd SET @UTCDateTime = DATEADD (hour, -5, @UTCDateTime)
    ELSE SET @UTCDateTime = DATEADD (hour, -6, @UTCDateTime)
    RETURN @UTCDateTime –Value is now CDT despite the name
    END
    GO

    Easy to adapt to other timezones.

    I hope people find that useful.

Leave a Reply