Updating Year Problem - 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

General SQL Server Forums

New to SQL Server Programming

Updating Year Problem

Author

Topic

khanss
Starting Member

4 Posts

Posted - 2006-04-11 : 10:30:02

Hi,

Please help me In sql server I am trying to update only year in a datetime column datatype

update events
set Year([field time]) = '2006'
where [event id] = 1559537

Server: Msg 174, Level 15, State 1, Line 1
The year function requires 1 arguments.

SamC
White Water Yakist

3467 Posts

Posted - 2006-04-11 : 10:34:57

DATETIME columns do not have "Year" functions with 1 or any number of parameters. I got lost with your problem specification there.

If you have a DATETIME column, setting it to the year 2006 will require a month, day and time also. Something like

SET MyDateTimeColumn = 'Jan 1 2006' -- would work fine.

khanss
Starting Member

4 Posts

Posted - 2006-04-11 : 10:49:00

HI SAM,

But I Have 100's of rows to be updated regardless of date and month i want to update only year ...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-11 : 10:51:45

If you want to change only year part, this is one of the methods

Select cast(stuff(convert(varchar,datecol,100),8,4,'2006') as datetime) from events where [event id] = 1559537

If you are happy with the results retured then run

Update events set datecol=cast(stuff(convert(varchar,datecol,100),8,4,'2006') as datetime) where [event id] = 1559537

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-04-11 : 11:00:03

Or maybe:


Select [field time], [NewDateTime] = 
-- UPDATE U SET [field time] =
     DATEADD(Year, 2006-DATEPART(Year, [field time]), [field time])
from events AS U
where [event id] = 1559537

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-11 : 11:04:11

Yours is more effecient method Kris