Converting Date Strings (in different formats) Into Data Type In SQL Server

Question

I have a column of data in the below string formats

When I copy and paste the above into an Excel sheet and change the column to a date format, all of the above will be converted into the same format correctly. Is there a similar feature in SQL server? I've tried using CAST and CONVERT, but have ran into conversion failed errors.

Follow-up

I found the values causing standard CAST and CONVERT functions to fail. There's some rows with decimal and int values as follows:

Answer 1

I ended up writing a CASE statement to account for the different format variations:

CASE
    WHEN [CommissionStart Date] LIKE '%NULL%'
    THEN '1/1/1900'
    WHEN ISDATE([CommissionStart Date]) = 1
    THEN CAST([CommissionStart Date] AS DATE)
    ELSE CONVERT(DATETIME, CAST([CommissionStart Date] AS DECIMAL(11, 5)))
END

Answer 2

Cast or Convert should work provided the dates are all in a valid format. If a value can't be parsed as a date, there's no magic fix. You have to find them and deal with them manually. The IsDate() function can help you find them.

select * from MyTable where IsDate(TextDateColumn) = 0

IsDate() will return 0 for numeric values like 4567 or 5456.6826 so if you have a lot of those then you can also check IsNumeric()

select * from MyTable where IsDate(TextDateColumn) = 0 and IsNumeric(TextDateColumn) = 0

That should allow you to identify the unparseable dates that are causing the conversion errors.