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

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

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:

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.