Search code examples
datereporting-servicesparametersexpressiondefault

SSRS Default Start/End Dates Return a period of specific dates

I'm trying to write an expression in SSRS:

If today's DAY is <= 15th day, return pay period previous month 16th to last day of month If today's DAY is >= 16th day, return current month day 1st to 15th

This is the expression I have now but its not working:

Start Date: 

=IIF(DAY(TODAY) <= 15, DateAdd(DateInterval.Day, 1-Day(Today), Today), DateAdd(DateInterval.Month, -1, (DateAdd(DateInterval.Day, 16-Day(Today), Today)))),
IIF(DAY(TODAY) >= 16, dateadd("m",0,dateserial(YEAR(Today),MONTH(Today),1)

End Date: 

=IIF(DAY(TODAY) <= 15, DateAdd(DateInterval.Day, -1, DateSerial(Year(Date.Now), Month(Date.Now), 1)),
IIF(DAY(TODAY) >= 16, dateadd(dd,datediff(dd,datepart(dd,getdate()),15),getdate())
Solution

  • I just had to do something similar in the past week...

    --StartDate, if current day is more than 16, set to 1, else 16
=iif(DatePart("d",Today) >= 16,  DateSerial(Year(Today), Month(Today), "1").AddMonths(-1), DateSerial(Year(Today), Month(Today),"16").AddMonths(-1))

--EndDate, if current day is more than 16 set to 15, else month end
=iif(DatePart("d",Today) >= 16,  DateSerial(Year(Today), Month(Today), "15").AddMonths(-1), DateSerial(Year(Today), Month(Today),"1").AddDays(-1))