SQL Server with French_CI_AS Collation date conversion
This is not a collation issue. This is a language / locale / culture issue. Your DB (or your login's default language) is set to &quot;French&quot; (most likely) and they use Day then Month formats, not Month then Day, hence your statement is attempting to use month 13.
Try this to reproduce:
SET LANGUAGE French;
DECLARE @datevar DATETIME = '2020-10-13 04:42:10';
/*
Le paramètre de langue est passé à Français.
Msg 242, Level 16, State 3, Line XXXXX
La conversion d'un type de données varchar en type de données datetime a
    créé une valeur hors limites.
*/

GO
PRINT '-----';
GO

SET LANGUAGE English;
DECLARE @datevar DATETIME = '2020-10-13 04:42:10';
GO

You have two options for changing this:

SET LANGUAGE
This controls not just the date format but also currency symbol, day and month names, etc. This might not be the best choice given that it controls more than just the date format, but it might be easier if it only requires updating the login's default language instead of updating a lot of code to do option # 2.

SET DATEFORMAT
This controls just the date format:
SET LANGUAGE French;
DECLARE @datevar DATETIME = '2020-10-13 04:42:10';
/*
Le paramètre de langue est passé à Français.
Msg 242, Level 16, State 3, Line XXXXX
La conversion d'un type de données varchar en type de données datetime a
    créé une valeur hors limites.
*/

SET DATEFORMAT ymd;
DECLARE @datevar2 DATETIME = '2020-10-13 04:42:10';

SELECT @datevar AS [@datevar], @datevar2 AS [@datevar2];
/*
@datevar    @datevar2
NULL        2020-10-13 04:42:10.000
*/



 
Regarding your other questions:

If the format is dmy, shouldn't both dates fail?

and

(implied) Why do DATETIME and DATETIME2 appear to parse the same date differently?

All of that is because working with dates is really messy. The documentation for DATEFORMAT states not only that:

datetime and smalldatetime interpretations may not match date, datetime2, or datetimeoffset

but also:

Some character string formats, for example ISO 8601, are interpreted independently of the DATEFORMAT setting.

While the documentation doesn't tie those two statements together, testing does show that ISO 8601 formatted dates (e.g. YYYY-MM-DD) are &quot;interpreted independently of the DATEFORMAT setting&quot; only for DATE, DATETIME2, and DATETIMEOFFSET. That is why the date of '2020-10-13' respects the value of DATEFORMAT for your DATETIME variable (and hence receives an error for cultures where the day comes before the month, whether separating date parts with &quot;-&quot; or &quot;/&quot;), but is parsed correctly for your DATETIME2 test (and always will regardless of the current culture or value of DATEFORMAT).
P.S. I would not consider a variant / SQL_VARIANT / object, etc to be the &quot;right&quot; type ;-)