CODEWITHSUNDEEP
sqlsql-server
Pier
Pier

Reputation: 37

Conversion date in SQL

I have a problem with date in SQL server management studio. A column contains 2 type of date, for example: '20/06/2020' and '2021-03-25'. The column have as data_type varchar(10) and I must convert in date. I have used this comand:

select cast(column_name as date) column_name
        from dataset

ERROR:

Conversion failed when converting date and/or time from character string

How to can I resolve it?

Upvotes: 0

Views: 73

Answers (2)

Peter B
Peter B

Reputation: 24147

You can check the content and convert accordingly:

SELECT CASE WHEN column_name LIKE '%/%'
            THEN CONVERT(DATE, column_name, 103)
            ELSE CONVERT(DATE, column_name, 120)
       END AS ConvertedDate
FROM DATASET

For available date format values, see the docs.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269643

You can use try_convert() and coalesce():

select coalesce(try_convert(date, col),       -- flexible, handles many formats
                try_convert(date, col, 103)   -- handles dd/mm/yyyy
               )

Note: You may have other formats that this doesn't handle, so this might still return NULL.

Upvotes: 4

Related Questions