update year in date column - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

update year in date column

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-10-22 : 04:45:56

Hi

I have dates such as 2006-09-08 00:00:00.000 and i need to update the year from 2006 to 2012.

How can i do that? I tried
update candidate_rbs set year(Accrediation_date)=2012 but it is incorrect.

Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-22 : 04:52:55

just add 6 years to it.


update candidate_rbs 
set    Accrediation_date = dateadd(year, 6, Accrediation_date)

KH
[spoiler]Time is always against us[/spoiler]

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-10-22 : 05:03:40

Thanks for the help.
If i want to set a year from 2007 to 2005 then would i do
update candidate_rbs
set Accrediation_date = datediff(year, 2, Accrediation_date)

?

Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-22 : 06:31:07

quote:
Originally posted by collie

Thanks for the help.
If i want to set a year from 2007 to 2005 then would i do
update candidate_rbs
set Accrediation_date = dateadd(year, -2, Accrediation_date)

?

Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

Madhivanan

Failing to plan is Planning to fail

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-10-22 : 06:48:59

Thanks :-)

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-22 : 06:54:55

more on dates
www.sql-server-performance.com/fk_datetime.asp

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-22 : 08:43:09