CODEWITHSUNDEEP
functiondatereporting-services
niemoy
niemoy

Reputation: 87

How to get SSRS Date to default correctly?

I have been using SSRS for years and have always used the function to set a defualt date parameter for last day of last month using the following. =dateadd("m",0,dateserial(year(Today),month(Today),0)) However, this time I need to do it for 9 months in the past so with it being the month of March 2021, I want the default date to be 6/30/2020 so I am using: =dateadd("m",-8,dateserial(year(Today),month(Today),0)) SSRS returns the correct year and month but I am getting the date as the 28th of June which I assume is because it is looking at Feb first and then going back 8 months? If I change the 0 or the 8 to any number I still get 28 in the date field?

Upvotes: 1

Views: 256

Answers (2)

Alan Schofield
Alan Schofield

Reputation: 21683

=DateAdd("d", -1,
        ( 
            DateAdd("m",
                    -8, 
                    DateSerial(YEAR(Today()), Month(Today()),1)
                    )
        )
    )

Starting from the inside, the DateSerial gets the first day of this month, giving us 2021-03-01 then we subtract 8 months from this giving us 2020-07-01 and then finally subtract 1 day from this to give us the last day of the previous month, 2020-06-30

Upvotes: 1

Hannover Fist
Hannover Fist

Reputation: 10860

I usually subtract the day number from the month to get the first of the month, subtract the number of months plus 1 then subtract a day to get the last day of the previous month. A little tedious but it avoids the various end of month issues.

=DATEADD("d", -1, DATEADD("M", 1 - 9, DATEADD("d", 1 - DAY(TODAY), TODAY)))

or you can do the same thing with the VB.NET methods:

=TODAY.AddDays(1 - TODAY.Day).AddMonths(-8).AddDays(-1)

Upvotes: 1

Related Questions