when try to convert varchar data type (mixed date formats) to a datetime data type resulted in an out-of-range value

Question

These are some values stored in my started_at column:

31/5/2022 10:27 
1/5/2022 17:03
24/6/2022 9:54 
.
.
.
2022-08-09 19:09:55 
2022-08-18 14:58:56
2022-11-17 22:50:52

There are more than 1'000'000 rows and have mixed date formats like these 2.

So when I execute this SQL to try and convert to datetime datatype:

SELECT 
    CONVERT(VARCHAR(30), CAST(started_at AS DATETIME), 103) 
FROM 
    [dbo].[testCyclistic12m]

I get this error:

Msg 242, Level 16, State 3, Line 39
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

I don't know why....

Can anyone help me? I'd appreciate it very much

Answer 1

You have an extraordinarily difficult problem to solve in a robust way. You want "garbage in, good data out".

CAST(started_at AS DATETIME) is unsuitable for this problem because it doesn't ingest all imaginable date / time text formats. You need somehow to detect which date format is present in each row and use the appropriate style value in CONVERT(DATETIIME, started_at, style).

Without knowing how many different text formats are in your table, it's difficult to give you more specific advice.

Answer 2

Something like this perhaps:

SELECT  COALESCE(try_convert(datetime, col1, 103), try_convert(datetime, col1, 121 )) AS converted_col1
,   col1
FROM    (
    VALUES  (N'31/5/2022 10:27 ')
    ,   (N'1/5/2022 17:03')
    ,   (N'24/6/2022 9:54 ')
    ,   (N'2022-08-09 19:09:55 ')
    ,   (N'2022-08-18 14:58:56')
    ,   (N'2022-11-17 22:50:52')
) t (col1)

It tries to cast to dd/mm/yyyy style first, and the regular yyyy-mm-dd in the second.

Output:

converted_col1	col1
2022-05-31 10:27:00.000	31/5/2022 10:27
2022-05-01 17:03:00.000	1/5/2022 17:03
2022-06-24 09:54:00.000	24/6/2022 9:54
2022-09-08 19:09:55.000	2022-08-09 19:09:55
2022-08-18 14:58:56.000	2022-08-18 14:58:56
2022-11-17 22:50:52.000	2022-11-17 22:50:52