Solved

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

Posted on 2012-04-10
5
1,730 Views
Last Modified: 2012-04-11
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
0
Comment
Question by:thomas-sherrouse
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 37828042
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
 
LVL 21

Assisted Solution

by:Jason Yousef, MS
Jason Yousef, MS earned 200 total points
ID: 37828109
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
 
LVL 37

Expert Comment

by:ValentinoV
ID: 37831243
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
 
LVL 12

Accepted Solution

by:
Harish Varghese earned 300 total points
ID: 37831458
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
 

Author Closing Comment

by:thomas-sherrouse
ID: 37832466
Great Thanks! - Both of these worked
0

Featured Post

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question