CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Dave.Gugg
Dave.Gugg

Reputation: 6781

Convert inconsistent date strings into datetime, ignore bad values

I have a couple badly managed varchar fields that have a bunch of dates (as well as some garbage data) in them. Unfortunately dates have been entered into these field in both the month/day/year and day/month/year formats. There are several specific formats including d/m/yy, d.mm.yyyy, d/m/yyyy, dd/mm/yy, etc.

I know storing dates this way is dumb but this is a third party's user defined field, so there is no way to ensure consistent formats. Here is sample query, [SRC User 5] and [SRC User 6] are the varchar "date" fields:

WITH    cte
          AS ( SELECT   [SRC Source code] ,
                        [SRC Description] ,
                        REPLACE([SRC User 5], '.', '/') AS [SRC User 5] ,
                        REPLACE([SRC User 6], '.', '/') AS [SRC User 6] ,
                        [SRC Mailing date] ,
                        [Date of first order] ,
                        [CMP Company]
               FROM     DatabaseName.dbo.Source_LWT
               WHERE    ISDATE([SRC User 5]) = 1
                        AND ISDATE([SRC User 6]) = 1
             )
    SELECT  [SRC Source code] ,
            [SRC Description] ,
            [SRC User 5] AS [Start Date] ,
            [SRC User 6] AS [End Date] ,
            [SRC Mailing date] AS [In Home Date] ,
            [Date of first order] AS [First Order]
    FROM    cte
    WHERE   [SRC User 5] <= GETDATE()
            AND [SRC User 6] >= GETDATE()
            AND YEAR([SRC User 5]) > 13
            AND [CMP Company] = 820
    ORDER BY [SRC User 6]

Right now I'm getting an error, I'm thinking caused by the day/month/year format:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

How can I extract the most valid dates from this field as possible, assuming most the values will be month/day/year?

Upvotes: 1

Views: 1579

Answers (1)

Mohammed
Mohammed

Reputation: 313

Thanks For asking: Please try to replace your query with below, hope its works:

WITH    cte
      AS ( SELECT   [SRC Source code] ,
                    [SRC Description] ,
                    Cast(REPLACE([SRC User 5], '.', '/') as datetime) AS [SRC User 5] ,
                    Cast(REPLACE([SRC User 6], '.', '/') as datetime) AS [SRC User 6] ,
                    [SRC Mailing date] ,
                    [Date of first order] ,
                    [CMP Company]
           FROM     DatabaseName.dbo.Source_LWT
           WHERE    ISDATE([SRC User 5]) = 1
                    AND ISDATE([SRC User 6]) = 1
         )
SELECT  [SRC Source code] ,
        [SRC Description] ,
        [SRC User 5] AS [Start Date] ,
        [SRC User 6] AS [End Date] ,
        [SRC Mailing date] AS [In Home Date] ,
        [Date of first order] AS [First Order]
FROM    cte
WHERE   [SRC User 5] <= GETDATE()
        AND [SRC User 6] >= GETDATE()
        AND right(YEAR([SRC User 5]),2) > 13
        AND [CMP Company] = 820
ORDER BY [SRC User 6]

Good Luck

Upvotes: 0

Related Questions