Update Year of Datefield column with 2008 in MS SQL

Hi,

I have this Date Field in my table in DATETIME datatype and I would like to update the year with 2008 of the entire table.
I tried this but it did not work
Update Productivity
 set RecCreateDate="2008"
where RecCreateDate is not null

It gave an Error.
Could you please give me a correct SQL statement for this
SriCharyAsked:
Who is Participating?
 
tbsgadiConnect With a Mentor Commented:
UPDATE Mytable
SET RecCreateDate= DATEADD(YEAR, 2008-DATEPART(YEAR, RecCreateDate), RecCreateDate)
0
 
tbsgadiCommented:
Hi,
DATETIME datatype holds a whole date like 1/1/2008 not just the year.

Gary
0
 
SriCharyAuthor Commented:
Hi tbsgadi,

Yes DateTime datatype holds a whole date and I want to update just the year in it.

For eg: If I have a date "01/15/2003"
I want to update it to "01/15/2008"

Any options please.

Thanks
0
 
TimSledgeConnect With a Mentor Commented:
See the attached code snippet.  You would need to run one iteration for each year currently in the table.
create table dbo.tblTest (TestDate DATETIME)
 
INSERT INTO dbo.tblTest (TestDate) Select '1/1/2009'
 
UPDATE dbo.tblTest
SET TestDate = DATEADD(YEAR, -1, TestDate)
WHERE DATEPART(YEAR, TestDate)=2009
 
SELECT TestDate
FROM dbo.tblTest

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.