Converting multiple date formats to a DateTime value in SQL Server

Question

I'm writing a SQL script to migrate data from an old table to a new one, and in the process, to put some rigour into the data by using proper data-types.

There's a varchar field in the old table which holds dates in various formats. So far I've discovered at least 6 formats for the dates in this field:

dd/mm/yyyy
dd/mm/yy
dd.mm.yy
dd-mm-yyyy
ddxx mmmm yyyy
dd.m.yy

I want to convert all of these dates into proper date-time values.

The post here about the CONVERT function is very helpful.

However, I'm wondering how to go about detecting which of the above formats apply for each row being processed.

One option is to execute a CONVERT for each possible format within a TRY/CATCH block and just use whichever one doesn't fail.

The only other option might be string manipulation to detect the format, but that might be awfully difficult to write myself. If that was the only option I'd probably ditch SQL and write the migration as a C# application (only then it would take much longer to run than a native SQL Script).

Does anyone know of an easier way of doing this in SQL Server?

Answer 1

My solution to the problem, so far, is to use nested try/catch blocks to attempt a CONVERT on every potential date format.

Failing that, for unusual date formats such as '12th August 2011', I resorted to manual string parsing (which turned out to be easier than I'd expected).

Here's the code I'm using, but for someone else's particular needs it would have to be adapted accordingly. (It's assumed that the table variable '@datetable' has had its [DOB] field populated from the database.)

DECLARE @dateTable TABLE
(
    [DOB] varchar(max),
    [DOBReal] datetime,
    [DOBFilled] bit
)

DECLARE @id INT

WHILE (SELECT COUNT(*) FROM @dateTable Where [DOBFilled] = 0) > 0
BEGIN
    SELECT TOP 1 @Id = ID From @dateTable Where [DOBFilled] = 0

    DECLARE @sourceString VARCHAR(100) = 
        (SELECT TOP 1 [DOB] 
        FROM @dateTable
        WHERE ID = @Id)

    DECLARE @result DATETIME = NULL

    BEGIN TRY
        SET @result = CONVERT(DATETIME, @sourceString, 3)
    END TRY
    BEGIN CATCH
        BEGIN TRY
            SET @result = CONVERT(DATETIME, @sourceString, 103)
        END TRY
        BEGIN CATCH
            BEGIN TRY
                SET @result = CONVERT(DATETIME, @sourceString, 4)
            END TRY
            BEGIN CATCH
                BEGIN TRY
                    SET @result = CONVERT(DATETIME, @sourceString, 104)
                END TRY
                BEGIN CATCH
                    BEGIN TRY
                        SET @result = CONVERT(DATETIME, @sourceString, 5)
                    END TRY
                    BEGIN CATCH
                        BEGIN TRY
                            SET @result = CONVERT(DATETIME, @sourceString, 105)
                        END TRY
                        BEGIN CATCH
                            BEGIN TRY
                                SET @result = CONVERT(DATETIME, @sourceString, 6)
                            END TRY
                            BEGIN CATCH
                                BEGIN TRY
                                    SET @result = CONVERT(DATETIME, @sourceString, 106)
                                END TRY
                                BEGIN CATCH
                                    IF (SUBSTRING(@sourceString, 3, 1) = ' ' 
                                        AND SUBSTRING(@sourceString, 6, 1) = ' ')
                                    BEGIN
                                        IF (LEN(@sourceString) = 8)
                                        BEGIN
                                            SET @result = 
                                                CONVERT(
                                                    DATETIME, 
                                                    (SUBSTRING(@sourceString, 1, 2) + '/' + 
                                                    SUBSTRING(@sourceString, 4, 2) + '/' +
                                                    SUBSTRING(@sourceString, 7, 2)),
                                                    3)
                                        END
                                        ELSE
                                        IF (LEN(@sourceString) = 10)
                                        BEGIN
                                            SET @result = 
                                                CONVERT(
                                                    DATETIME, 
                                                    (SUBSTRING(@sourceString, 1, 2) + '/' + 
                                                    SUBSTRING(@sourceString, 4, 2) + '/' +
                                                    SUBSTRING(@sourceString, 7, 4)),
                                                    103)
                                        END
                                    END
                                    ELSE 
                                    IF (LEN(@sourceString) >= 11 
                                        AND ISNUMERIC(SUBSTRING(@sourceString, 1, 1)) = 1)
                                    BEGIN
                                        DECLARE @date VARCHAR(100) = SUBSTRING(@sourceString, 1, 1)

                                        IF (ISNUMERIC(SUBSTRING(@sourceString, 2, 1)) = 1)
                                        BEGIN
                                            SET @date = SUBSTRING(@sourceString, 1, 2)
                                        END

                                        SET @date = 
                                            @date + '/' +
                                            CASE 
                                                WHEN CHARINDEX('January', @sourceString) > 0 THEN '01'
                                                WHEN CHARINDEX('February', @sourceString) > 0 THEN '02'
                                                WHEN CHARINDEX('March', @sourceString) > 0 THEN '03'
                                                WHEN CHARINDEX('April', @sourceString) > 0 THEN '04'
                                                WHEN CHARINDEX('May', @sourceString) > 0 THEN '05'
                                                WHEN CHARINDEX('June', @sourceString) > 0 THEN '06'
                                                WHEN CHARINDEX('July', @sourceString) > 0 THEN '07'
                                                WHEN CHARINDEX('August', @sourceString) > 0 THEN '08'
                                                WHEN CHARINDEX('September', @sourceString) > 0 THEN '09'
                                                WHEN CHARINDEX('October', @sourceString) > 0 THEN '10'
                                                WHEN CHARINDEX('November', @sourceString) > 0 THEN '11'
                                                WHEN CHARINDEX('December', @sourceString) > 0 THEN '12'
                                            END

                                        IF (ISNUMERIC(RIGHT(@sourceString, 4)) = 1)
                                        BEGIN
                                            SET @date = @date + '/' + RIGHT(@sourceString, 4)
                                        END

                                        BEGIN TRY
                                            SET @result = CONVERT(DATETIME, @date, 103)
                                        END TRY
                                        BEGIN CATCH

                                        END CATCH
                                    END
                                END CATCH
                            END CATCH
                        END CATCH
                    END CATCH
                END CATCH
            END CATCH
        END CATCH
    END CATCH

    UPDATE @dateTable 
    SET [DOBReal] = @result, [DOBFilled] = 1
    WHERE [ID] = @Id

END

SELECT * FROM @dateTable

Again, if anyone knows of a better solution or some built-in SQL Server function that can handle this more elegantly, I'm all ears.