SQL Syntax - convert varchar yymmdd to varchar yyyymmdd

Hello experts,

I have a desire to convert a column called recall_date to varchar yyyymmdd, which currently is formatted yymmdd.  

The recall_date column has plenty of null values so we want to filter those out. When I run the below script I receive the error:

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

I realize that my 103 is a datetime convert - but I can go from datetime to yyyymmdd which is why I did that.  There is likely an easier way.

Thanks!

update demo_out2
set recall_date = 
case when recall_date is not null 
and len(recall_date) = 6
then CONVERT(varchar, CAST(recall_date AS DATETIME), 103) 
else '' end

Open in new window

robthomas09Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
add  and isdate(recall_date)=1

to the case statement    (maybe = 0)

hth
0
pcelbaCommented:
If you need to convert yymmdd date to yyyymmdd and NULL date to empty string then you may do it following way (suppose recall_date is varchar(8) already).

Note:
By default, SQL Server interprets two-digit years based on a cutoff year of 2049. That is, the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950. Many client applications, such as those based on Automation objects, use a cutoff year of 2030. SQL Server provides the two digit year cutoff configuration option that changes the cutoff year used by SQL Server and allows for the consistent treatment of dates. We recommend specifying four-digit years.

UPDATE demo_out2 
   SET recall_date = ISNULL(convert(varchar(8), convert(datetime, recall_date, 12), 112), '')
 WHERE len(recall_date) = 6 OR recall_date IS NULL

Open in new window

0
robthomas09Author Commented:
Ill give it a shot and let you know - thanks!
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

robthomas09Author Commented:
I am still receiving the error:

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

Does this mean that there might possible be data out of order in the current format that is 6 characters in length?  I cant find any, but is it trying to convert 14 to a month, for example, and giving the above error?

Thanks!
0
SharathData EngineerCommented:
try this
UPDATE demo_out2 
SET    recall_date = CASE 
                       WHEN Isdate(recall_date) = 1 
                            AND Len(recall_date) = 6 THEN Convert(VARCHAR,Convert(DATE,recall_date),112) 
                       ELSE '' 
                     END

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
robthomas09Author Commented:
Last piece code got it - thanks all!
0
SharathData EngineerCommented:
If that works, can you provide the reason for 'B' rating?
0
SharathData EngineerCommented:
ohh.. I over looked at the rating. Apologies for the confusion.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.