Converting to UTC Time

In preparation for a migration to Azure SQL Database, we found we had to adjust our date formats to UTC time as this is the way dates are stored in azure because your databases could be all over the world.  For new systems, this is very useful; however, what about migrating data to Azure?  I have tables with dates based on a GETDATE() default and this time will be the time zone for the server where the record was created.  I wanted to convert my dates to UTC time and came up with this script.  It is based on servers in the EASTERN time zone.  As the date for daylight savings time changes each year, I used timeanddate.com for the historical data.

Suppose I have a table named Customers with a column called CreationDate and I wanted to update my times to UTC. I would apply this logic. As always, test before implementation. I put an ELSE statement at the end so I could check to see if any dates fell outside my range. This only goes back to 2009. You would have to consult timeanddate.com for more dates if you have older data.

UPDATE Customers
SET CreationDate =  CASE
	WHEN YEAR(CreationDate) = 2009 AND CreationDate BETWEEN '2009-03-08 02:00:00.0000000' AND '2010-11-01 02:00:00.0000000' THEN DATEADD(hh,6,CreationDate)
	WHEN YEAR(CreationDate) = 2009 AND CreationDate NOT BETWEEN '2009-03-08 02:00:00.0000000' AND '2010-11-01 02:00:00.0000000' THEN DATEADD(hh,5,CreationDate)
	WHEN YEAR(CreationDate) = 2010 AND CreationDate BETWEEN '2010-03-14 02:00:00.0000000' AND '2010-11-07 02:00:00.0000000' THEN DATEADD(hh,6,CreationDate)
	WHEN YEAR(CreationDate) = 2010 AND CreationDate NOT BETWEEN '2010-03-14 02:00:00.0000000' AND '2010-11-07 02:00:00.0000000' THEN DATEADD(hh,5,CreationDate)
	WHEN YEAR(CreationDate) = 2011 AND CreationDate BETWEEN '2011-03-13 02:00:00.0000000' AND '2011-11-06 02:00:00.0000000' THEN DATEADD(hh,6,CreationDate)
	WHEN YEAR(CreationDate) = 2011 AND CreationDate NOT BETWEEN '2011-03-13 02:00:00.0000000' AND '2011-11-06 02:00:00.0000000' THEN DATEADD(hh,5,CreationDate)
	WHEN YEAR(CreationDate) = 2012 AND CreationDate BETWEEN '2012-03-11 02:00:00.0000000' AND '2012-11-04 02:00:00.0000000' THEN DATEADD(hh,6,CreationDate)
	WHEN YEAR(CreationDate) = 2012 AND CreationDate NOT BETWEEN '2012-03-11 02:00:00.0000000' AND '2012-11-04 02:00:00.0000000' THEN DATEADD(hh,5,CreationDate)
	WHEN YEAR(CreationDate) = 2013 AND CreationDate BETWEEN '2013-03-10 02:00:00.0000000' AND '2013-11-04 02:00:00.0000000' THEN DATEADD(hh,6,CreationDate)
	WHEN YEAR(CreationDate) = 2013 AND CreationDate NOT BETWEEN '2013-03-10 02:00:00.0000000' AND '2013-11-04 02:00:00.0000000' THEN DATEADD(hh,5,CreationDate)
	WHEN YEAR(CreationDate) = 2014 AND CreationDate BETWEEN '2014-03-09 02:00:00.0000000' AND '2014-11-02 02:00:00.0000000' THEN DATEADD(hh,6,CreationDate)
	WHEN YEAR(CreationDate) = 2014 AND CreationDate NOT BETWEEN '2014-03-14 02:00:00.0000000' AND '2014-11-02 02:00:00.0000000' THEN DATEADD(hh,5,CreationDate)
	WHEN YEAR(CreationDate) = 2015 AND CreationDate BETWEEN '2015-03-08 02:00:00.0000000' AND '2015-11-01 02:00:00.0000000' THEN DATEADD(hh,6,CreationDate)
	WHEN YEAR(CreationDate) = 2015 AND CreationDate NOT BETWEEN '2015-03-08 02:00:00.0000000' AND '2015-11-01 02:00:00.0000000' THEN DATEADD(hh,5,CreationDate)
	WHEN YEAR(CreationDate) = 2016 AND CreationDate BETWEEN '2016-03-13 02:00:00.0000000' AND '2016-11-06 02:00:00.0000000' THEN DATEADD(hh,6,CreationDate)
	WHEN YEAR(CreationDate) = 2016 AND CreationDate NOT BETWEEN '2016-03-13 02:00:00.0000000' AND '2016-11-06 02:00:00.0000000' THEN DATEADD(hh,5,CreationDate)
	ELSE '2046-01-01 11:07:20.6500000'
	END
FROM Customers

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