Convert SQL Azure GetDate() UTC Time to Local Time

Posted on July 22, 2012
With 3 Comments
Written by Joey Iodice

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.

DATEADD (hh, n, GETDATE())

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.

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

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- 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

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

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.

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

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =================================================

-- 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

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

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.

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

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =================================================

-- Author: Joey Iodice

-- Create date: 07/20/2012

-- Description: Converts UTC time into Central Time

-- =================================================

CREATE FUNCTION [dbo].[GetLocalDateTime] ()

RETURNS DATETIME

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

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.

Google+

Donate Bitcoins

3 Comments

Alex Edwards says:

September 20, 2012 at 12:18 pm - Reply

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.
- Joey Iodice says:
  
  September 20, 2012 at 1:41 pm - Reply
  
  Thanks Alex for your suggestions. I will try to simplify it when I get some time.
Alex Edwards says:

September 24, 2012 at 11:57 am - Reply

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.