• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1996
  • Last Modified:

Expression to Find Previous Sunday - SQL Server 2008 R2 Report Builder

I've looked around and can't quite find what I need in terms of an expression that will display the Previous Sunday's Date in a Report on SQL Server 2008 R2 Reporting Services Report.

I've got:

= dateadd(dd,(datediff(dd,-53684,a.DATE)/7)*7,-53684)

and it's giving me the error of "dd" isn't declared and "a" isn't declared. Instead of troubleshooting and wasting time I decided to ask the Experts here. Could someone help me out with this.

2 Solutions
a quick test suggests that what you have basically works as is:

  SELECT dateadd(dd,(datediff(dd,-53684,getdate())/7)*7,-53684)

perhaps the error you are seeing is with some other part of the query?
Jason Yousef, MSSr. BI  DeveloperCommented:
I do believe you need the SSRS expression, right?  if not, ignore my reply then...

you can do something like that

=switch (
Weekday(today) = 1, today,
Weekday(today) = 2, dateadd("d", -1, today),
Weekday(today) = 3, dateadd("d", -2, today),
Weekday(today) = 4, dateadd("d", -3, today),
Weekday(today) = 5, dateadd("d", -4, today),
Weekday(today) = 6, dateadd("d", -5, today),
Weekday(today) = 7, dateadd("d", -6, today)

Open in new window

ValentinoVBI ConsultantCommented:
For an equivalent SSRS expression compared to what you've got, try this:

=DateAdd("d", CLng(DateDiff("d", "1753-01-07", Fields!TheDate.Value)/7)*7, "1753-01-07")

Some explanation:

  o the number -53684 converts to a particular Sunday far away in the year 1753. To avoid implicit conversion (which works differently in SSRS), I've pre-translated it in the expression.

  o the division by 7 will keep the decimals as well, unlike in SQL.  To remove them I've added a CLng conversion.

  o "d" in SSRS is the equivalent of dd in the SQL statement, note especially the double quotes.

  o Fields!TheDate.Value refers to a dataset field called TheDate and should contain a datetime (or date)
Harish VargheseProject LeaderCommented:
Huslayer's suggesion above is the best and simplest in terms of Report Expression. You can also simplify it as below:

=dateadd ("d", -(Weekday(today) -1), today)

thomas-sherrouseAuthor Commented:
Great Thanks! - Both of these worked
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now