Solved

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

Posted on 2012-04-10
5
1,637 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
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

856 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