Can a year or Month be updated in a date field? 1

[bobpv]

Greetings,

I want to try to update some date values in a test database on SQL 2000 server. I would assume that this would work:
update JobTable
set datepart(yyyy,jobdate) = '2009'

But no, syntax error Incorrect syntax near '('.

I have tried a few other ways, with again no success. Perhaps this just cannot be done?

I humbly beg advice form the experts on this forum!

 

[gmmastros]

I would...

Calculate the number of years that need to be added to each date. Then, use the DateAdd function to add that number to the job date.

To test the code...

Select jobdate,
       DateAdd(Year, 2009 - DatePart(Year, JobDate), JobDate) As NewDate
From   JobTable

If this returns the correct date, then convert this to an update statement, like this:

Update JobTable
Set    JobDate = DateAdd(Year, 2009 - DatePart(Year, JobDate), JobDate)

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Perfect sense, as always. I read it and was going to suggest it, but you already did.

 

[bobpv]

Sweet! Like a charm, many thanks. I was going about it from a wrong point of view. I really appreciate the assist!