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.

Thanks
thomas-sherrouseAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

knightEknightCommented:
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?
0
Jason YousefSr. 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

0
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)
0
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)

Thanks,
Harish
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thomas-sherrouseAuthor Commented:
Great Thanks! - Both of these worked
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.