How to standardise a column of mixed date formats in T-SQL

Question

Got a table in SQL Server which contains a varchar column with date data. Unfortunately the dates are in a whole slew of different formats.

2012-05-01
27/05/2012 
07MAY2014
19/07/13   

There may be others, but that's all I've encountered so far.

I need to squeeze these into a datetime column into another table, so I've been trying to select them as standard date-time values. At first, I thought that'd be easy:

UPDATE myTable 
SET myDateColumn = CONVERT(DATETIME, myDateColumn, 103)
WHERE ISDATE(myDateColumn) = 0

But the trouble is that SQL Server treats dd/mm/yy and dd/mm/yyyy as separate formats. The former is code 3, and the latter is code 103. So whichever way I run that update, it chokes on the opposite format.

Is there any way I can select/update based on the date format, and get all these dates converted to a single valid DateTime format?

Answer 1

My guess is that you just have to try to differentiate between the different classes and handle each case in the appropriate way. Something like this:

declare @tab table (d varchar(20))
insert @tab values ('2012-05-01'),('27/05/2012'),('07MAY2014'),('19/07/13')

select 
    case 
        when isnumeric(left(d,4)) = 1 then cast(d as date) 
        when len(d) = 10 then convert(date, d, 103) 
        when len(d) = 8 then convert(date, d, 3) 
        when charindex('/',d) = 0 and isnumeric(d) = 0 then convert(date, d, 106) 
    end as [date]
from @tab

Output:

date
----------
2012-05-01
2012-05-27
2014-05-07
2013-07-19

It might not be that efficient, but I presume this is a one-off operation. I didn't write it as an update statement, but the query should be easy to adapt, and you should consider adding the converted date as a new proper datetime column if possible in my opinion.

Edit: here's the corresponding update statement:

update @tab
set d = 
    case 
        when isnumeric(left(d,4)) = 1 then cast(d as date) 
        when len(d) = 10 then convert(date, d, 103) 
        when len(d) = 8 then convert(date, d, 3) 
        when charindex('/',d) = 0 and isnumeric(d) = 0 then convert(date, d, 106) 
    end 
from @tab

Answer 2

This is totally horrid, but it works with your example:

DECLARE @DodgyDates TABLE (
    DateString VARCHAR(50));
INSERT INTO @DodgyDates VALUES ('2012-05-01');
INSERT INTO @DodgyDates VALUES ('27/05/2012'); 
INSERT INTO @DodgyDates VALUES ('07MAY2014');
INSERT INTO @DodgyDates VALUES ('19/07/13');
SELECT * FROM @DodgyDates;
--SELECT CONVERT(DATE, DateString) FROM @DodgyDates;--Fails
WITH DateDeconstruct AS (
    SELECT
        *,
        CASE
            WHEN DateString LIKE '____-__-__' THEN DateString
            WHEN DateString LIKE '__/__/____' THEN RIGHT(DateString, 4) + '-' + SUBSTRING(DateString, 4, 2) + '-' + LEFT(DateString, 2)
            WHEN DateString LIKE '__/__/__' THEN '20' + RIGHT(DateString, 2) + '-' + SUBSTRING(DateString, 4, 2) + '-' + LEFT(DateString, 2)
            WHEN DateString LIKE '_________' THEN RIGHT(DateString, 4) + '-' + CONVERT(VARCHAR(2), DATEPART(MM, DateString)) + '-' + LEFT(DateString, 2)
        END AS FixedString
    FROM
        @DodgyDates)
SELECT
    DateString AS OriginalDate,
    FixedString AS FixedDate,
    CONVERT(DATE, FixedString) AS ConvertedDate
FROM
    DateDeconstruct;

Results are:

OriginalDate    FixedDate   ConvertedDate
2012-05-01  2012-05-01  2012-05-01
27/05/2012  2012-05-27  2012-05-27
07MAY2014   2014-5-07   2014-05-07
19/07/13    2013-07-19  2013-07-19