SSRS Add Months in current date with last day of month

0
votes

I want to add months with last day in current date by using

=dateadd(dateinterval.month, +4, DateAdd("d",-(Day(today)), Today))

expression.

output is

current_date = 12/02/2014

finish_date = 03/30/2014

The problem is that finsih_date month is 03(March) and last day of March is 31 but my parameter showing 30.

1
visual-studio-2010reporting-servicesparametersssrs-2008
No ssrs at hand at the moment, but try to construct a new date by using 01.<current month + 1>. <Current year> this is the first day of the next month after your finished_date. Now you can simply call date add with -1 on this new date which should result in the last day of the previous month. – The-First-Tiger
The-First-Tiger plz can u write complet expression for finish_date ..? – Asif Mehmood

1 Answers

0
votes

It may look like:

=DateAdd("d", -1, DateSerial(DatePart("yyyy", finish_date), DatePart("m", DateAdd("m", 1, finish_date)), 1))

For every day in a given month it calculates the last day of the given month. So for 03/30/2014 you would get 03/31/2014.

Calculation works like: Build new date which is set to the first day of the following month of a given date. Substract 1 day. Which is the last day of the month before. As we added 1 month it is the last day of the month of the given date.

Edit

Code with finished_date = TODAY + 4 Month (finished_date + 1 so it is TODAY + 5)

=DateAdd("d", -1, DateSerial(DatePart("yyyy", DateAdd("m", 5, TODAY())), DatePart("m", DateAdd("m", 5, TODAY())), 1))