How to convert varchar into datetime and skip not valid datetime in TSQL

Question

I have a database in SQL Server. I do not have authorization to change any table but I can create views.

I have three varchar columns which store dates in the format YYYYMMDD. I want to create a view where the dates are converted from varchar to datetime.

Now it can be the case that instead of a date in this columns I can have empty space, NULL or the character -.

Let's take the closedDate column. If I use CONVERT(datetime, closedDate, 112)

1. Valid dates are converted correctly,

2. When there is empty space the date is converted to 1900-01-01,

3. NULL is kept,

4. The - causes a conversion error:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

How can I tell to the function to convert dates just if it has valid date form and leave NULL or better an empty date(or space) in all the other cases?

Answer 1

Try this

CASE WHEN ISDATE(closedDate) = 1 THEN CONVERT(datetime, closedDate, 112) 
     ELSE NULL END closedDate