Conversion failed when converting date and/or time from character string. when using union all in sql server

Question

I have this script for having totals on the left side and bottom part of my pivoted table, I've got this error of 'Conversion failed when converting date and/or time from character string'. on line 19, but the line of code there is nothing to do with conversion. the individual select works fine, but if they will be UNION they got this error.

DECLARE @sizenameS NVARCHAR (MAX)
SELECT @sizenameS = COALESCE(@sizenameS + ', ','') + QUOTENAME(SizeName)
FROM
(   select DISTINCT SIZESET.SizeSetID, SizeName
    from SIZESET
    left join SHIPMENTSIZESET ON SIZESET.SizeSetID = SHIPMENTSIZESET.SizeSetID
    left join SHIPMENTSCHED on SHIPMENTSCHED.ShipmentSchedID = SHIPMENTSIZESET.ShipmentSchedID 
    left join DESIGN on DESIGN.DesignID = SIZESET.DesignID 
    left join ARTICLE on ARTICLE.DesignID = DESIGN.DesignID where ArticleNo = 'EZT02'
)src2
ORDER BY SizeSetID


DECLARE @DownDownDown NVARCHAR (MAX)
SELECT @DownDownDown = COALESCE(@DownDownDown + 'ISNULL([' + CAST (SizeName AS VARCHAR) +'],0) + ', 'ISNULL([' + CAST(SizeName AS VARCHAR)+ '],0) + ')
    FROM
    (   select DISTINCT SIZESET.SizeSetID, SizeName
        from SIZESET
        left join SHIPMENTSIZESET ON SIZESET.SizeSetID = SHIPMENTSIZESET.SizeSetID
        left join SHIPMENTSCHED on SHIPMENTSCHED.ShipmentSchedID = SHIPMENTSIZESET.ShipmentSchedID 
        left join DESIGN on DESIGN.DesignID = SIZESET.DesignID 
        left join ARTICLE on ARTICLE.DesignID = DESIGN.DesignID where ArticleNo = 'EZT02'
    )src3
    ORDER BY SizeSetID
SET @DownDownDown = LEFT (@DownDownDown, LEN (@DownDownDown)-1)

DECLARE @ToTheLeft NVARCHAR(MAX)
SELECT @ToTheLeft = COALESCE(@ToTheLeft + ',ISNULL(SUM([' + CAST(SizeName AS VARCHAR)+']),0)', 'ISNULL(SUM([' + CAST(SizeName AS VARCHAR)+']),0)')
    FROM
    (   select DISTINCT SIZESET.SizeSetID, SizeName
        from SIZESET
        left join SHIPMENTSIZESET ON SIZESET.SizeSetID = SHIPMENTSIZESET.SizeSetID
        left join SHIPMENTSCHED on SHIPMENTSCHED.ShipmentSchedID = SHIPMENTSIZESET.ShipmentSchedID 
        left join DESIGN on DESIGN.DesignID = SIZESET.DesignID 
        left join ARTICLE on ARTICLE.DesignID = DESIGN.DesignID where ArticleNo = 'EZT02'
    )src
    ORDER BY SizeSetID

DECLARE @JerryPogi NVARCHAR (MAX)
SET @JerryPogi =    'SELECT *, ('+ @DownDownDown + ') AS [Grand Total] INTO #Pansamantala
            FROM
            (
                select DISTINCT StartDate, EndDate, SizeName, Quantity
                from SIZESET
                left join SHIPMENTSIZESET ON SIZESET.SizeSetID = SHIPMENTSIZESET.SizeSetID
                left join SHIPMENTSCHED on SHIPMENTSCHED.ShipmentSchedID = SHIPMENTSIZESET.ShipmentSchedID 
                left join DESIGN on DESIGN.DesignID = SIZESET.DesignID 
                left join ARTICLE on ARTICLE.DesignID = DESIGN.DesignID where ArticleNo = ''EZT02''
            ) JerryGwapo
            PIVOT
            (
                SUM(Quantity)
                FOR SizeName
                IN('+@sizenameS +')
            ) JerryHot
            ORDER BY StartDate

            SELECT  *  FROM #Pansamantala
            UNION ALL
            SELECT '''',''Grand Total'','+@ToTheLeft +',  isnull(SUM([Grand Total]),0)  FROM #Pansamantala

            DROP TABLE #Pansamantala'

            exec(@JerryPogi)

Answer 1

The second column of #Pansamantala is EndDate, which I presume is a date type. In your UNION ALL, you're placing the string 'Grand Total' into the second column, which is not a valid date/time string.

To resolve this, either remove 'Grand Total' in the second half of your UNION ALL:

SELECT '''','''','+@ToTheLeft +',  isnull(SUM([Grand Total]),0) 
FROM #Pansamantala

or CAST the date columns in the first half of your UNION ALL to strings:

SELECT CAST(StartDate as varchar), CAST(EndDate as varchar), SizeName, Quantity  
FROM #Pansamantala