Site icon Kitchens Doors Xpress

SQL Server Date Column Inconsistent

I have column date with string(nvarchar) format. and I have inconsistent column like this

I have more example like this

2021-7-01
2021-8-01

first I have try with CASE WHEN like this

SELECT 
    CASE 
    WHEN DATE_IN LIKE '[0-9]{4}-[0-9]{2}-[0-9]{2}'
                    THEN CONVERT(datetime, date_in, 102)
                    WHEN date_in LIKE '[0-9]{4}-[0-9]{2}-[0-9]{2}'
                    THEN CONVERT(datetime, date_in) END AS DATE_IN, EMP_NAME,DATE_IN
FROM staging.irisEtcSingleFile

but, the output is NULL .. I want the output is like this

2021-01-01
2021-07-01
2021-08-01

I really appreciate to get the answer .. Thanks before

Exit mobile version