Increase All Data in an SQL DateTime Column by a Year.

Posted by admin on Mar 16, 2010 in code, SQL, Testing, tips |

I was recently testing a rarely used piece of functionality on one of the sites which belong to the company I work for. The test DB was very old as releases for this part of the system come very few and far between.

The system is written in such a way that data is only returned to the end user for data for a the last 12 months , (1 Year). The database in question had not been updated for about 18 months and it would have taken about 5 days for me to get a decent backup from the live system restored to test.

The data in the Test Database was valid it was just a little too old to be returned via the front User Interface (GUI).

What I needed was a nice and easy way to bring the date column in the database forward by year.

Obviously for good practice I also requested the live DB dump to be sent over just to completeness of testing. But to test this piece of functionality it wasn’t needed.

The following is the SQL query (Transact-SQL) I cobbled together to get the job done.

It will do a select first to see if you have any data in a particular financial year (2009 and 2010).

It will then update all 2009 data to read 2010. So 01-Feb-2009 becomes 01-Feb-2010 and 23rd June 2009 becomes 23rd June 2010.

It will then carry out the same select query it did before. If all has gone well the 2009 data should be blank and the 2010 field should be populated.

In any update script I write I always input a select before the update and the same select after the update. This way I and any other users using my script can see if the update has worked.

Now the code.

SELECT     YEAR(ColumnName) AS ColumnNameYear
FROM         TableName
GROUP BY YEAR(ColumnName)
HAVING      (YEAR(ColumnName) = 2010) OR
(YEAR(ColumnName) = 2009)

UPDATE    TableName
Set  ColumnName = dateadd(yy,1, ColumnName)
WHERE     DATEPART(year, ColumnName) = ’2009′

SELECT     YEAR(ColumnName) AS ColumnNameYear
FROM         TableName
GROUP BY YEAR(ColumnName)
HAVING      (YEAR(ColumnName) = 2010) OR
(YEAR(ColumnName) = 2009)

Hope you find it useful.

And I’m sure there are many more ways of coding this much simplier but I’m a tester and not a coder and for me it works.

Reply

Copyright © 2012 The Test Manager Blog All rights reserved. Theme by Laptop Geek.