DROP TABLE IF EXISTS #12thMonthExpectedActivity
DECLARE @query AS varchar(max)
DECLARE @schemaName AS varchar(5) = 'wl03' -- To be used later as a parameter in stored procedure
DECLARE @plus11Month AS CHAR(8) = CONVERT(CHAR(8), CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())+11, 0) AS DATE), 112) --The 11th month from the current month
DECLARE @plus12Month AS CHAR(8) = CONVERT(CHAR(8), CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())+12, 0) AS DATE), 112) --The 12th month from the current month

-- Add rows for existing month into a temporary table
-- The code below returns an error: The data types varchar and date are incompatible in the add operator.
SET @query = 'SELECT CAST(''' + @plus12Month + ''' AS DATE) AS period, genusId, subjectId, waitingStageId, value, CAST(''' + CONVERT(CHAR(8), GETDATE(), 112) + ''' AS DATE) AS savedOn, ''<Automated>'' AS savedBy INTO #12thMonthExpectedActivity FROM [' + @schemaName + '].[ExpectedActivity] WHERE period in (''' + @plus11Month + ''')'
EXEC(@query)

-- this fails now, but it works fine if I don't do dynamic SQL
select * from #12thMonthExpectedActivity