Error with Dynamic SQL Data Transfer: Operand data type nvarchar is invalid for subtract operator

Question

I need to transfer data between tables in a database. The issue is that the old table uses a 6-character format (YYMMDD) for dates in the "RecordDate" column, whereas the new target table has this column defined as datetime.

When attempting to move the data using CONVERT(DATETIME, RecordDate), I encounter the following error:

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

To address this, I employed a CASE WHEN structure, and the data conversion was successful. Here is the working query:

SELECT 
CASE 
        WHEN ISDATE('20' + SUBSTRING(RecordDate, 1, 2) + '-' + SUBSTRING(RecordDate, 3, 2) + '-' + SUBSTRING(RecordDate, 5, 2)) = 1 
        THEN CONVERT(DATETIME, '20' + SUBSTRING(RecordDate, 1, 2) + '-' + SUBSTRING(RecordDate, 3, 2) + '-' + SUBSTRING(RecordDate, 5, 2))
        ELSE NULL 
        END AS RecordDate

FROM 
    sourceTable;

However, when integrating this into a dynamic query using sp_executesql, I encounter the error: "Operand data type nvarchar is invalid for subtract operator." This is due to the usage of the CASE statement in the dynamic query. Here's the relevant dynamic query section:

DECLARE @sql NVARCHAR(MAX);
SET @sql = '
INSERT INTO targetTable 
(RecordDate, ...other columns...)
SELECT
    (CASE 
        WHEN ISDATE(''20'' + SUBSTRING(RecordDate, 1, 2) + '-' + SUBSTRING(RecordDate, 3, 2) + '-' + SUBSTRING(RecordDate, 5, 2)) = 1 
        THEN CONVERT(DATETIME, ''20'' + SUBSTRING(RecordDate, 1, 2) + '-' + SUBSTRING(RecordDate, 3, 2) + '-' + SUBSTRING(RecordDate, 5, 2))
        ELSE NULL 
    END) AS RecordDate,
    ...other columns...
FROM  sourceTable
';
EXEC sp_executesql @sql;

However, the usage of CASE in the dynamic query triggers the error. How can I resolve this issue and successfully execute the dynamic query for data transfer?