CODEWITHSUNDEEP
sql-server-2008
wootscootinboogie
wootscootinboogie

Reputation: 8695

Add X number of years to a datetime variable SQL Server 2008

I need to add 10 years to the year portion of the date with date stored in the format yyyy-mm-dd for everyone who visited in the year 2010. Below is the query I want to try but I don't know how to use the rollback function of SQL Server yet. Visityear is varchar datatype.

update myTable
set admitdate=dateadd(yyyy,10,admitdate)
where visitYear='2010'

Upvotes: 1

Views: 7885

Answers (2)

marc_s
marc_s

Reputation: 754678

If you want to do it on your table:

BEGIN TRANSACTION

UPDATE dbo.myTable
SET admitdate = DATEADD(YEAR, 10, admitdate)
WHERE visitYear = '2010'

SELECT visityear, admitdate 
FROM dbo.myTable
WHERE visitYear = '2010'

ROLLBACK TRANSACTION

Of course - if you want to actually apply the update, use COMMIT instead of ROLLBACK ....

Upvotes: 1

inevio
inevio

Reputation: 887

In SQL Server 2008 all queries are part of a transaction that is implicitly committed. I'm assuming you are okay with using the dateadd function, what you have looks correct. So to modify to allow rollback you could do the following - although not sure when / why you would rollback, that logic would need to be added.

BEGIN TRANSACTION addDateYears
update myTable
set admitdate=dateadd(yyyy,10,admitdate)
where visitYear='2010'

/* IF SOMETHING ROLLBACK TRANSACTION addDateYears */

COMMIT TRANSACTION addDateYears

Upvotes: 2

Related Questions