CODEWITHSUNDEEP
sql-serverreporting-servicesexpression
user3566128
user3566128

Reputation: 13

SSRS Date Expression stopped working

I hope someone can share their experience with me. I've used the following ssrs expression to default a SSRS report parameter to the last day of the current month for months without issue:

=DateAdd(“d”, -1, DateSerial(Year(Now()), Month(Now()), 1))

This morning instead of returnng 3/31/2016, the expression is stuck on 3/29/2016. Can anyone help me understand why this happened?

Upvotes: 0

Views: 152

Answers (2)

Chris Latta
Chris Latta

Reputation: 20560

The expression you show will give the last day of the previous month, not the last day of this month.

You want to do date arithmetic rather than build dates from scratch. Think about what will happen in December if you are using the current year but adding a month to find the month you need.

The safest way to get the last day of the current month would be to calculate the first day of this month, add one month then subtract a day:

=DateAdd(DateInterval.Day, -1, DateAdd(DateInterval.Month, 1, DateAdd(DateInterval.Day, 1-Day(Today), Today)))

If what you actually want is the last day of the previous month, then that is simple:

=DateAdd(DateInterval.Day, 0-Day(Today), Today)

Upvotes: 0

alejandro zuleta
alejandro zuleta

Reputation: 14108

You are substracting one day to the first day of the current month. You have to get the first day of the next month and substract one day.

=DateAdd("d", -1, DateSerial(2016, Month(Now.AddMonths(1)), 1))

It returns 3/31/2016

Let me know if this helps.

Upvotes: 1

Related Questions