conversion of a varchar data type to a datetime data type resulted in an out-of-range value

Question

conversion of a varchar data type to a datetime data type resulted in an out-of-range value

Triality

SSCommitted

Points: 1958
More actions
April 25, 2012 at 8:26 am

#256967

Hello -
I am trying to run the following query but keep getting an error:
UPDATE dbo.Test_Data
SET SERVICE_DATE =
CASE
WHEN YEAR(SERVICE_DATE) < 1975
THEN '2010-'+RIGHT(LEFT(convert(VARCHAR(50),service_date,121),10),5)
WHEN YEAR(SERVICE_DATE) >= 1975
THEN '2011-'+RIGHT(LEFT(convert(VARCHAR(50),service_date,121),10),5)
END
The error says:
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.
The statement has been terminated.
Can anyone assist?
Thanks!

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 1

Can you paste some records that service_data column has? I replaced your Service_date with getdate() and it worked fine.

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Triality SSCommitted Points: 1958 More actions · Answer 2

Sure, I have added an attachment with the top 10,000 rows service_dates. Thanks!

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 3

The datatype of this column in table is datetime, right? if not then there might be some values which might not get convered to dateime datatype.

I picked some values, and they seem to be working fine.

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Triality SSCommitted Points: 1958 More actions · Answer 4

Triality

SSCommitted

Points: 1958

April 25, 2012 at 8:41 am

#1479048

Yes, the datatype is datetime for service_date

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Try one the following (or both):

First prior to the update, SET DATEFORMAT mdy;

Or make the following changes to your code:

REPLACE( '2010-'+RIGHT(LEFT(convert(VARCHAR(50),service_date,121),10),5),'-','')

REPLACE('2011-'+RIGHT(LEFT(convert(VARCHAR(50),service_date,121),10),5),'-','')

Triality SSCommitted Points: 1958 More actions · Answer 6

Lynn - I have tried both and it did not help. Thanks for the thought!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Lynn Pettis

SSC Guru

Points: 442467

April 25, 2012 at 8:46 am

#1479052

Please show me the code you used for both.

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 8

Just a shot in the dark

Check if you have any date like 29th February in your table

SELECT * FROM dbo.Test_Data WHERE DAY( service_date ) = 29 AND MONTH( service_date ) = 02

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Triality SSCommitted Points: 1958 More actions · Answer 9

Kingston - Yes there are many February 29 service_dates in the data.

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 10

Triality (4/25/2012)
Kingston - Yes there are many February 29 service_dates in the data.

You will have to handle these dates separately

You can't convert them to "2011-02-29" or "2010-02-29"

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Kingston Dhasian (4/25/2012)
Triality (4/25/2012)
Kingston - Yes there are many February 29 service_dates in the data.
You will have to handle these dates separately
You can't convert them to "2011-02-29" or "2010-02-29"

Good catch. Didn't even think of it. :blush:

Sean Lange SSC Guru Points: 286590 More actions · Answer 12

Well 2/29 is not valid in either 2010 or 2011. Looks like Kingston fired blindly and got a direct hit!!!

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Triality SSCommitted Points: 1958 More actions · Answer 13

Triality

SSCommitted

Points: 1958

April 25, 2012 at 9:06 am

#1479074

Kingston is the man. Thank you sir!

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 14

Triality (4/25/2012)
Kingston is the man. Thank you sir!

My brain cells didn't have much work today in office. Hence, its still fresh 😀

Glad to help you out 🙂

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/