I have a Varchar field that I want to convert to a smalldatetime
In the field the a records that contain string dates like 05/14/2020 and 'N/A'
I want to change the N/A to NULL
the easiest way was to create another field (smalldatetime and copy the one field to another, but I get
"The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value."
when I run:
SET Value1 = (CASE WHEN Value = 'N/A' THEN NULL ELSE cast(CONVERT(varchar, value, 101) as smalldatetime) END)
There is no other values in the varchar field than string dates and 'N/A'
Put a length to the convert varchar
Try the following and see if it helps find the offending row(s)
CASE WHEN TRY_CONVERT(smalldatetime, value) IS NULL
THEN 'Cast failed'
ELSE 'Cast succeeded'
END AS Result
where value <> 'N/A'
select * from src where Result = 'Cast failed'
You can do this:
SELECT Result = try_cast(nullif(value, 'N/A') As date)
Any NULL values returned will either be N/A or were not able to be converted to a date.
The easiest way is TRY_CAST, if you're on a version of SQL that supports it, which I think is SQL 2012 or later.
SET Value1 = TRY_CAST(Value1 AS smalldatetime)
TRY_CAST will automatically set the result to NULL if is is not valid.