sqlreporting-services

SSRS Leap Year Determination with DateAdd

I have a report that compares year over year changes for any date range within a year. Thus, in my Reporting Services report, I have two parameters @StartDate and @EndDate that the user supplies. I also have two hidden parameters (@PriorStartDate, @PriorEndDate) for the previous year with default values that are programatically determined using an expression such as this.

=DateAdd(DateInterval.Year,-1,Parameters!StartDate.Value)

I recently ran into a problem when 2/1/17-2/28/17 generated previous dates of 2/1/16-2/28/16. Unfortunately there was a record on 2/29/16 that did not get picked up due to the leap year. What's the most concise way to set my previous date while determining if a day should be added based upon leap year?

Solution

  • You can check if the year is leap before calculate the previous year date. So for @StartDate it would be.

    =IIF(
(((Parameters!StartDate.Value.Year - 1) Mod 4 = 0 and
(Parameters!StartDate.Value.Year - 1) Mod 100 <> 0) or
(Parameters!StartDate.Value.Year - 1) Mod 400 = 0) and
 Parameters!StartDate.Value.Month = 2 and Parameters!StartDate.Value.Day = 28,
Parameters!StartDate.Value.AddYears(-1).AddDays(1),
Parameters!StartDate.Value.AddYears(-1)
)

    Replicate the same solution for @EndDate.