Solved

How to replace the year in a date in SQL Server

Posted on 2014-12-16
8
135 Views
Last Modified: 2014-12-16
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
0
Comment
Question by:romsom
8 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 200 total points
ID: 40503061
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%'

SELECT * FROM #tmp

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

0
 
LVL 18

Assisted Solution

by:Simon
Simon earned 150 total points
ID: 40503070
update vacationBid
set day1=dateadd(yy,1,day1)
where year(day1)=2014
0
 
LVL 22

Accepted Solution

by:
plusone3055 earned 150 total points
ID: 40503073
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%'
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:romsom
ID: 40503090
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.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40503118
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.
0
 
LVL 22

Expert Comment

by:plusone3055
ID: 40503127
JH

I liked the old school overalls pic .. why so formal now ?
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40503210
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.
0
 
LVL 22

Expert Comment

by:plusone3055
ID: 40503260
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 :)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Syntax: How to force case sensitive query? 2 56
PROPERCASE SCRIPT IN SQL 3 23
Disable TLS1.0 on Win 2012 server 7 60
SQL Select Query help 1 37
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question