asked on

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

knightEknight

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?

SOLUTION

Jason Yousef

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ValentinoV

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)

ASKER CERTIFIED SOLUTION

Harish Varghese

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

thomas-sherrouse

ASKER

Great Thanks! - Both of these worked