SQLTeam.com | Weblogs | Forums

What is the easiest way to convert a field to smalldatetime

#1

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'

#2

Put a length to the convert varchar

1 Like
#3

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'
#4

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.

#5

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.

UPDATE dbo.tblTrkCustomDate
SET Value1 = TRY_CAST(Value1 AS smalldatetime)

TRY_CAST will automatically set the result to NULL if is is not valid.