CODEWITHSUNDEEP
sql-serverdatetimetype-conversion
CiccioMiami
CiccioMiami

Reputation: 8266

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

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?

Upvotes: 5

Views: 15277

Answers (2)

Shridhar
Shridhar

Reputation: 2468

        Use below code    


    SELECT CASE WHEN ISDATE(datecolumn)=1 THEN CONVERT(datetime, datecolumn, 103 ) 
                        ELSE null END
            FROM tablename


 use below for empty data

    SELECT CASE WHEN ISDATE(datecolumn)=1 THEN CONVERT(datetime, datecolumn, 103 ) 
                            ELSE '' END
                FROM tablename

Upvotes: 0

rs.
rs.

Reputation: 27457

Try this

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

Upvotes: 17

Related Questions