robthomas09
asked on
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!
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Last piece code got it - thanks all!
If that works, can you provide the reason for 'B' rating?
ohh.. I over looked at the rating. Apologies for the confusion.
ASKER