CODEWITHSUNDEEP
sql-servert-sql
Ken
Ken

Reputation: 1539

Converting varchar column to datetime when multiple formats were used

I have a column startDate in tbMyTable that was set up incorrectly as a varchar. Most of the data in this column is good, but multiple datetime formats are used. A few rows of the table just have junk in the column.

Please help me write a sql script that will convert the column from varchar(50) to a nullable DateTime - saving the data that can be converted and setting the bad data to null.

Ken

Upvotes: 1

Views: 1726

Answers (1)

Gaston Flores
Gaston Flores

Reputation: 2467

Check this t-sql script (its works with different string dates format):

SELECT CASE WHEN ISDATE(a.field) = 1 THEN CONVERT (DATETIME, a.field, 101) ELSE NULL END field
FROM (
    SELECT '2012-06-06' as field
    UNION
    SELECT null as field
    UNION
    SELECT '2012-06-06asdad' as field
    UNION
    SELECT '2012/06/06' as field 
) a

For more information of the CONVERT function check this LINK. The function ISDATE returns 1 when is a valid date.

Upvotes: 2

Related Questions