• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1356
  • Last Modified:

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

0
robthomas09
Asked:
robthomas09
3 Solutions
 
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
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
 
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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now