I have cross applied a table valued function in a DML statement that returns two columns one of which is RiskValue (which is an integer denoting Scan Period) Now when I print the value RiskValue along with dateadd function like this (y is function alias and am is another table)
select cast(y.RiskValue as int),dateadd(m,cast(y.RiskValue as int),@RunningDate)
from .....
it gives me proper values as
6 | 'Some Date'
but when I use it in a where clause as
where am.DateOpen >= dateadd(m,cast(y.RiskValue as int),@RunningDate)
I get the Error :
Adding a value to a 'datetime' column caused an overflow
Note that passing hard coded values as
where am.DateOpen >= dateadd(m,6,@RunningDate)
works fine. (Obviously it will)
Any suggestions what might be wrong?
Posting Aaron Bertrand's comment as answer so that people reaching this question will find it helpful :
RiskValue is 6 for that row, but you have to understand that SQL Server may not optimize the statement in the same order you wrote it. It can often error out trying to perform calculations on values that should have been excluded by a filter, but the calculations were attempted first. We can try
DATEADD(MONTH, CASE WHEN y.RiskValue < 20000 THEN y.RiskValue END, @RunningDate)