SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Adding 1 year to all dates, but messed up
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

meef
Yak Posting Veteran

97 Posts

Posted - 02/19/2013 :  12:41:28  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3652 Posts

Posted - 02/19/2013 :  12:44:43  Show Profile  Reply with Quote
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.

Edited by - James K on 02/19/2013 12:45:51
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/19/2013 :  12:47:48  Show Profile  Reply with Quote
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/

Go to Top of Page

meef
Yak Posting Veteran

97 Posts

Posted - 02/19/2013 :  12:51:23  Show Profile  Reply with Quote
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 :)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/19/2013 :  12:53:59  Show Profile  Reply with Quote
yep...it will only affect BTSInvDt column as that was the target of your update

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

Go to Top of Page

meef
Yak Posting Veteran

97 Posts

Posted - 02/19/2013 :  13:04:47  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/19/2013 :  13:07:04  Show Profile  Reply with Quote
cool
glad tht you got it sorted

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000