How to replace the year in a date in SQL Server

Hi Experts,
I would like to replace the year part in a date in SQL Server.
I've tried the following, but I get this error message:
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

UPDATE VacationBid
   SET Day1 = REPLACE(Day1,'2014','1015')
 WHERE Day1 LIKE '%2014%'

Open in new window

Thank you for your help
Who is Participating?
plusone3055Connect With a Mentor Commented:
you really want the year to read 1015 ???

or was that a typo ?
i think your looking for 2015

UPDATE VacationBid
   SET Day1 = DATEADD(YEAR,+1,Day1)
 WHERE Day1(YEAR) LIKE '%2014%'
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Perhaps the medievial cultures in the year 1015 do not wish you to change dates.

Works on my 2008R2 box, assuming that the column you're trying to change is a date:
CREATE TABLE #tmp (dt date) 

INSERT INTO #tmp (dt) 
VALUES ('2014-01-01'), ('2014-03-01'), ('2010-01-07'), ('2009-01-01')

UPDATE #tmp 
SET dt = REPLACE(dt, '2014', '2015') 
WHERE dt LIKE '%2014%'


Open in new window

Perhaps it's a leap year issue, as the below returns a type conversion error
SELECT CAST('1015-02-29' as date) 

Open in new window

SimonConnect With a Mentor Commented:
update vacationBid
set day1=dateadd(yy,1,day1)
where year(day1)=2014
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

romsomAuthor Commented:
Very funny!
My users didn't want to travel back in time just to take vacation in year 1015.
Sorry, it was just a typo.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
We aim to please.

Every once in awhile we get a question about Access 200, and it takes awhile to explain that the Romans' first couple of desktop database applications left a lot to be desired, and that 202 was really the first usable application.

I liked the old school overalls pic .. why so formal now ?
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
When you blow two grand on family photos there's a strong desire to justify spending the money.

The previous photo was after a Tough Mudder I ran a couple of years ago.
If you think it's a better photo for EE then I'll put it back.  
You're the first one that's commented on it.
I hear that
that's why all family photos are taken with our cells :)

coming from the guy DJ'ing in his picture so what do I know :)
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.