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.
|
1 |
DATEADD (hh, n, GETDATE()) |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Joey Iodice (JoeyIodice.com) -- Credits: Tim Cullen (MSSQLTips.com) -- Create date: 07/20/2012 -- Description: Calculates the second Sunday in -- March at 8:00AM UTC (2:00AM CST) -- ============================================= CREATE FUNCTION [dbo].[DaylightSavingsStart] (@Year VARCHAR(4)) RETURNS SMALLDATETIME AS BEGIN DECLARE @StartWeek SMALLDATETIME SET @StartWeek = '03/01/' + CONVERT(VARCHAR,@Year) RETURN CASE DATEPART(dw,@StartWeek) WHEN 1 THEN DATEADD(HOUR,176,@StartWeek) WHEN 2 THEN DATEADD(HOUR,320,@StartWeek) WHEN 3 THEN DATEADD(HOUR,296,@StartWeek) WHEN 4 THEN DATEADD(HOUR,272,@StartWeek) WHEN 5 THEN DATEADD(HOUR,248,@StartWeek) WHEN 6 THEN DATEADD(HOUR,224,@StartWeek) WHEN 7 THEN DATEADD(HOUR,200,@StartWeek) END END GO |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ================================================= -- Author: Joey Iodice (JoeyIodice.com) -- Credits: Tim Cullen (MSSQLTips.com) -- Create date: 07/20/2012 -- Description: Calculates the first Sunday in -- November at 7:00AM UTC (2:00AM CDT) -- ================================================= CREATE FUNCTION [dbo].[DaylightSavingsEnd] (@Year VARCHAR(4)) RETURNS SMALLDATETIME AS BEGIN DECLARE @EndWeek SMALLDATETIME SET @EndWeek = '11/01/' + CONVERT(VARCHAR,@Year) RETURN CASE DATEPART(dw,DATEADD(WEEK,1,@EndWeek)) WHEN 1 THEN DATEADD(HOUR,7,@EndWeek) WHEN 2 THEN DATEADD(HOUR,151,@EndWeek) WHEN 3 THEN DATEADD(HOUR,127,@EndWeek) WHEN 4 THEN DATEADD(HOUR,103,@EndWeek) WHEN 5 THEN DATEADD(HOUR,79,@EndWeek) WHEN 6 THEN DATEADD(HOUR,55,@EndWeek) WHEN 7 THEN DATEADD(HOUR,31,@EndWeek) END END GO |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ================================================= -- Author: Joey Iodice -- Create date: 07/20/2012 -- Description: Converts UTC time into Central Time -- ================================================= CREATE FUNCTION [dbo].[GetLocalDateTime] () RETURNS DATETIME AS BEGIN DECLARE @StartDate SMALLDATETIME, @EndDate SMALLDATETIME, @Date DATETIME SET @Date = GETUTCDATE() SET @StartDate = dbo.DaylightSavingsStart(CONVERT(VARCHAR,DATEPART(YEAR,GETDATE()))) SET @EndDate = dbo.DaylightSavingsEnd(CONVERT(VARCHAR,DATEPART(YEAR,GETDATE()))) IF @Date between @StartDate and @EndDate BEGIN SET @Date = DATEADD (hh, -5, GETUTCDATE()) END ELSE BEGIN SET @Date = DATEADD (hh, -6, GETUTCDATE()) END RETURN @Date END GO |
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.
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.
Thanks Alex for your suggestions. I will try to simplify it when I get some time.
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.