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.
[sourcecode language=”sql”]
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
[/sourcecode]
Leave a Reply
You must belogged in to post a comment.