Solved

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

Posted on 2012-04-10
5
1,555 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:huslayer
huslayer 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.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

708 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now