Search code examples
sql-serverreporting-servicesdatediff

SSRS - DateValue

in SSRS, i am trying to return a DateValue that is the first day of the next month after the selected month/year. Everything works with my formula except when i try to evaluate on the month of December. The formula works for all other months (evaluating on "4" yields an output of 1/1/(year selected +1) for example), but December returns a #Error every time.

My formula is:

=IIF(Parameters!Month.Value="12",(DateValue("1" & "/" & Parameters!Year.Value+1)),(DateValue(Parameters!Month.Value+1 & "/" & Parameters!Year.Value)))
Solution

  • I would look at using DateAdd and DateSerial.

    So an expression like:

    =DateAdd(DateInterval.Month
  , 1
  , DateSerial(Parameters!Year.Value, Parameters!Month.Value, 1))

    This uses DateSerial to construct the first of the month for the selected Year and Month, then uses DateAdd to add another month to this.

    Worked fine on a basic report I made to test the expression:

    enter image description here

    enter image description here