Adding 1 year to all dates, but messed up

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Adding 1 year to all dates, but messed up

Author

Topic

meef
Posting Yak Master

113 Posts

Posted - 2013-02-19 : 12:41:28

I have a dummy table here with 27000 records and they all had dates from 2008 through 2012. I wanted to go through and add one year to each column, using this line:

UPDATE DemoData
SET BTSInvDt = DATEADD(year, 1, BTSInvDt)

Now that worked fine, but then I was an idiot and only changed the second variable (in this case, the second BTSINVDT) and now they all have the same year. What's the best way to get a range of dates back into these records?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-19 : 12:44:43

quote:
only changed the second variable (in this case, the second BTSINVDT)

So what was the query that you actually ran? There is no easy way to roll back the changes you already committed. You can try to restore a backup to another location and try to retrieve the correct data from the restored copy. Unless you have another table in the database that can be used to compute what the dates should be, that would be your best bet.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-19 : 12:47:48

if your attempt is to identify the records then use logic like

SELECT *
FROM DemoData
WHERE BTSInvDt = DATEADD(year,1,yournewvariable)

and it will show you all you updated by mistake

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

meef
Posting Yak Master

113 Posts

Posted - 2013-02-19 : 12:51:23

I ran each datetime field with this:

UPDATE DemoData
SET BTSInvDt = DATEADD(year, 1, BTSInvDt)

So the next one I ran was this:

UPDATE DemoData
SET BTSInvDt = DATEADD(year, 1, otherdatefield1)

then

UPDATE DemoData
SET BTSInvDt = DATEADD(year, 1, otherdatefield2)

Etc. etc. I left BTSInvDt in the beginning for each one instead of changing both of them.

Oddly enough, somehow everything looks good again, maybe it didn't change everything I thought it did and only did the BTSInvDt column.

This probably doesn't make any sense, but bottom line I think all is good :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-19 : 12:53:59

yep...it will only affect BTSInvDt column as that was the target of your update

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

meef
Posting Yak Master

113 Posts

Posted - 2013-02-19 : 13:04:47

Yeah, so when I went back to BTSInvDt to just fix the month/date (because everything was the same day/month/year in the field), I took the data from another date field minus one week so it added all the proper years and months back in there too.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-19 : 13:07:04

cool
glad tht you got it sorted

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources