0

I can't save this view because of the error. I'm not using cast because I thought it wasn't necessary. I'm new to T-SQL and am very unclear on how to track down the source of hte error since SSMS doesn't give any hints:

SELECT        TOP (100) PERCENT TLInstance, Data, MONTH(Timestamp - 1) AS RPTMth, Timestamp
FROM            dbo.VIEW_TLData
WHERE        (Timestamp BETWEEN CONVERT(DATE, { fn CONCAT(CONVERT(NVARCHAR, CASE WHEN MONTH(GETDATE()) = 1 THEN YEAR(GETDATE()) - 1 ELSE YEAR(GETDATE()) END), '-02-01') }, 102) AND CONVERT(DATE, 
                         { fn CONCAT(CONVERT(NVARCHAR, CASE WHEN MONTH(GETDATE()) = 1 THEN YEAR(GETDATE()) ELSE YEAR(GETDATE()) END + 1), '-02-01') }, 102)) AND (DAY(Timestamp) = 1) OR
                         (Timestamp BETWEEN CONVERT(DATE, { fn CONCAT(CONVERT(NVARCHAR, CASE WHEN MONTH(GETDATE()) = 1 THEN YEAR(GETDATE()) - 1 ELSE YEAR(GETDATE()) END), '-02-01') }, 102) AND CONVERT(DATE, 
                         { fn CONCAT(CONVERT(NVARCHAR, CASE WHEN MONTH(GETDATE()) = 1 THEN YEAR(GETDATE()) ELSE YEAR(GETDATE()) END + 1), '-02-01') }, 102)) AND (DAY(Timestamp) = 1) AND (TLInstance = '238' OR
                         TLInstance BETWEEN '255' AND '259' OR
                         TLInstance = '289' OR
                         TLInstance = '421' OR
                         TLInstance = '426' OR
                         TLInstance = '427' OR
                         TLInstance = '428' OR
                         TLInstance = '429' OR
                         TLInstance = '430' OR
                         TLInstance = '431' OR
                         TLInstance = '433' OR
                         TLInstance = '441' OR
                         TLInstance = '442' OR
                         TLInstance = '444' OR
                         TLInstance = '491')
ORDER BY TLInstance

Any idea what I am doing wrong?

efuddy
  • 105
  • 1
  • 3
  • 11
  • Hi, I think what is happening is that your date format of "02-01" is actually trying to perform a subtraction with the way the logic is set up. This is producing an integer result that is causing the error. Take a look at the following link for resolving: https://forums.asp.net/t/2072428.aspx?SQL+Between+Dates+Operand+type+clash+datetime2+is+incompatible+with+int+ – user3662215 Jan 30 '20 at 18:33
  • The error went away when I changed to: SELECT TOP (100) PERCENT TLInstance, Data, DATEADD(MONTH, - 1, Timestamp) AS RPTMth, Timestamp – efuddy Jan 30 '20 at 20:31

0 Answers0