SSRS Last Week Monday and SUnday

22
votes

I need the last week Monday and Sunday , say today is March 18th hence the SSRS expression should return March 10th -Monday March 16th -Sunday

I was able to get it using SQL 

select dateadd(d,(2-datepart(dw, getdate())), dateadd(ww,-1,getdate()))



select dateadd(d,(1-datepart(dw, getdate())), getdate())

not sure how to get it done via SSRS expression

2
reporting-services

2 Answers

41
votes

SSRS VBA allows very similar expressions for date manipulation to Sql, the main difference being the use of the DateInterval enum. Without checking your expressions, it will convert to SSRS VBA as follows:

Last Monday:

=DateAdd(DateInterval.Day, 2-WeekDay(Today), DateAdd(DateInterval.Day, -7, Today()))

Last Sunday:

=DateAdd(DateInterval.Day, 1-WeekDay(Today), Today())
0
votes

To get Sunday from Last Week : (Note: Last Week Sunday is different from Last Sunday).

=DateAdd(DateInterval.Day, 1-WeekDay(Today), DateAdd(DateInterval.Day, -7, Today))

To get Saturday from Last Week : 

=DateAdd(DateInterval.Day, 7-WeekDay(Today), DateAdd(DateInterval.Day, -7, Today))

with Weekdays values as below:
1 - Sunday
2 - Monday
3 - Tuesday
4 - Wednesday
5 - Thursday
6 - Friday
7 - Saturday