Expression type numeric is invalid for collate

Question

I am busy with a UNION ALL in SQL between different databases.

SELECT     CostValue AS [COST OF SALES], Customer AS [CUSTOMER CODE], DocumentType AS [DOCUMENT TYPE], Invoice AS [INVOICE NO], InvoiceDate AS [INVOICE DATE], 
                  DATEPART(dd, InvoiceDate) AS [INVOICE DAY], Mass AS MASS, NetSalesValue AS SALES, NetSalesValue - CostValue AS [GROSS PROFIT], OrderType, 
                  QtyInvoiced AS QUANTITY, SalesOrder, StockCode AS [STOCK CODE], TrnMonth AS [FIN MONTH], TrnYear AS [FIN YEAR], TrnYear * 100 + TrnMonth AS YYYYMM, 
                  'SHP' AS COMPANY
FROM         SomeCompanyA.dbo.SalesDetail
WHERE     (LineType = 1) AND (TrnYear >= 2010)
UNION ALL
SELECT     CostValue AS [COST OF SALES], Customer AS [CUSTOMER CODE],     DocumentType AS [DOCUMENT TYPE], Invoice AS [INVOICE NO], InvoiceDate AS [INVOICE DATE], 
                  DATEPART(dd, InvoiceDate) AS [INVOICE DAY], Mass AS MASS, NetSalesValue AS SALES, NetSalesValue - CostValue AS [GROSS PROFIT], OrderType, 
                  QtyInvoiced AS QUANTITY, SalesOrder, StockCode AS [STOCK CODE], TrnMonth AS [FIN MONTH], TrnYear COLLATE DATABASE_DEFAULT AS [FIN YEAR], (TrnYear * 100) COLLATE database_default + TrnMonth AS YYYYMM, 
                  'SGF' AS COMPANY
FROM         SQLXXXXXX.SomeCompanyB.dbo.SalesDetail AS SalesDetail_1
WHERE     (LineType = 1) AND (TrnYear >= 2010)

I am receiving the following error upon execution "Expression type numeric is invalid for COLLATE clause" which I think is coming from this (TrnYear * 100) COLLATE database_default + TrnMonth AS YYYYMM

I am not sure how else to COLLATE this as I understand this as a concatenation and not an implicit conversion.

Please could someone offer some advice as to how to cast this correctly.

I've also taken a look here Use Collate in CONCAT but I can't see how this would work as I'm using an operator on the year.

Perhaps I'm missing something. Any advice would be appreciated.

Thanks

Answer 1

COLLATE is for textual types, and (TrnYear * 100) evaluates to a numeric. Try converting to a varchar with CAST((TrnYear * 100) AS VARCHAR) before using the COLLATE.