April 25, 2012 at 8:26 am
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!
April 25, 2012 at 8:31 am
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.
April 25, 2012 at 8:36 am
Sure, I have added an attachment with the top 10,000 rows service_dates. Thanks!
April 25, 2012 at 8:40 am
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.
April 25, 2012 at 8:41 am
Yes, the datatype is datetime for service_date
April 25, 2012 at 8:41 am
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),'-','')
April 25, 2012 at 8:44 am
Lynn - I have tried both and it did not help. Thanks for the thought!
April 25, 2012 at 8:46 am
Please show me the code you used for both.
April 25, 2012 at 8:48 am
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
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 25, 2012 at 8:50 am
Kingston - Yes there are many February 29 service_dates in the data.
April 25, 2012 at 8:56 am
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"
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 25, 2012 at 9:01 am
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:
April 25, 2012 at 9:02 am
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/
April 25, 2012 at 9:06 am
Kingston is the man. Thank you sir!
April 25, 2012 at 9:12 am
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 🙂
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply