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

 All Forums
 SQL Server 2005 Forums
 Express Edition and Compact Edition (2005)
 How to change Dates in database

Author  Topic 

Starting Member

5 Posts

Posted - 2012-01-13 : 08:24:14
I have a database with Date field that needs to be changed from
1975 to 2011. Problem is, I only want to change the year, a not
the day and month.

In short change 1975\09\05 to 2011\09\05
1975\09\06 to 2011\09\06

Please help

Martin Hogan

Martin Hogan

Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-13 : 08:30:18
Depending on what the data type of the column is you could do it using dateadd or replace as shown below. If your column is datetime type (and assuming you want to replace all rows etc.)
UPDATE YourTable SET YourDateCol = DATEADD(YEAR,36,YourDateCol); -- add 36 years
If it is character type:
UPDATE YourTable SET YourDateCol = REPLACE(YourDateCol,'1975','2011');; -- replace 1975 with 2011
Go to Top of Page

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-14 : 01:03:17
same as

SQL Server MVP

Go to Top of Page

- Advertisement -