CODEWITHSUNDEEP
t-sqlreporting-servicesssrs-2008
user2146212
user2146212

Reputation: 93

How to get prior month data from a selected month

In SSRS, how would I return the prior month data of a selected month. month(lis.s_date=month(getdate()) -1 would get me the prior month data of the current month but I want it to be the prior month of any month selected in SSRS. What would I change getdate() to in order to achieve that?

Upvotes: 0

Views: 267

Answers (1)

Ian Preston
Ian Preston

Reputation: 39566

The equivalent SSRS expression would be:

=DateAdd(DateInterval.Month, -1, Now())

Edit after comment:

You can substitute any date value into the T-SQL expression, e.g a few ways here:

create table dates (dateValue date)

insert into dates select '01-jan-2013'
insert into dates select '01-feb-2013'
insert into dates select '15-feb-2013'

select dateValue
  , lastMonthDate = dateadd(mm, -1, dateValue)
  , lastMonthValue = month(dateadd(mm, -1, dateValue))
  , lastMonthName = datename(mm, dateadd(mm, -1, dateValue))
from dates

This just uses a table column instead of the getdate() function.

SQL Fiddle demo.

Upvotes: 1

Related Questions