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:
UPDATE dbo.tblTrkCustomDate
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'
Try the following and see if it helps find the offending row(s)
with src
as
(
SELECT
CASE WHEN TRY_CONVERT(smalldatetime, value) IS NULL
THEN 'Cast failed'
ELSE 'Cast succeeded'
END AS Result
from tblTrkCustomDate
where value <> 'N/A'
)
select * from src where Result = 'Cast failed'