mandania (7/3/2015)
they are both date type
Here's your error:
case
when Rownum<= datediff(day, salesdate, baseenddate) then DATEADD(mm, RowNum, salesdate)
else 0 end
You have two different datatypes returned by your CASE. Since date datatypes (returned by DATEADD) have a higher datatype precedence than INT, the ELSE condition will be changed to this:
else CAST(0 AS <<a date datatype>>) end
- which will fail with a DATE datatype but work with a DATETIME datatype.
The return datatype of DATEADD is the same as the date parameter passed in. If it's a DATE, as you confirm, then you get the error. When you pass a string into DATEADD the return datatype is DATETIME, and you don't get an error.
The solution?
Change ELSE 0 END
to ELSE <<a valid date of your choice, or NULL>> END.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden