Solved

SSRS  2005 does not filter dates correctly

Posted on 2011-09-13
2
315 Views
Last Modified: 2012-05-12
What is the correct syntax when you are trying to filter a range of dates? The parameter values I had sent down to @BegPostDate and @EndPostDate were '08/01/2011' and '08/31/2011' . What SSRS returned was 08/11/2011 and 08/25/2010. The same statement issued inside SQL returns only the 08/11/2011 record. How do I correct this statement inside BIDS VS2005 to do the same? I tried using format 102 and 112 and they return no records.
select pats.paprojnumber,pats.employid,rtrim(py.lastname)+', '+rtrim(frstname)+' '+rtrim(midlname) as Employee,pats.pacostcatid,convert(char(10),pahd.papd,101) as TrxDate,paextcost,@BegPostDate, convert(char(10),@BegPostDate,112)
from pa30101 pats, pa30100 pahd, upr00100 py
where  pats.patsno = pahd.patsno and convert(char(10),pahd.papd,101)>=@BegPostDate and convert(char(10),pahd.papd,101)<=@EndPostDate and pats.employid=py.employid and pats.pacostcatid in ('ENGINEER')
order by pats.paprojnumber,pats.pacostcatid,pats.employid,convert(char(10),pahd.papd,101)

Open in new window

0
Comment
Question by:rwheeler23
2 Comments
 
LVL 28

Accepted Solution

by:
sammySeltzer earned 500 total points
ID: 36530435
How about something like this?


select pats.paprojnumber,pats.employid,rtrim(py.lastname)+', '+rtrim(frstname)+' '+rtrim(midlname) as Employee,pats.pacostcatid,convert(char(10),pahd.papd,101) as TrxDate,paextcost,@BegPostDate, convert(char(10),@BegPostDate,112)
from pa30101 pats, pa30100 pahd, upr00100 py
where  pats.patsno = pahd.patsno 
and convert(Convert(Datetime,pahd.papd,101)>=Convert(Datetime,@BegPostDate, 101)) 
and (Convert(Datetime,pahd.papd,101)<=Convert(Datetime,@EndPostDate, 101)) 
and pats.employid=py.employid 
and pats.pacostcatid in ('ENGINEER')
order by pats.paprojnumber,pats.pacostcatid,pats.employid,pahd.papd

Open in new window

0
 

Author Closing Comment

by:rwheeler23
ID: 36530790
This is another reason why you should always used stored procedures. This was jsut a quickie little report someone wanted. You run the query in SQL and it returns the correct results.

After correcting a few syntax erros with your script the report works. Thanks.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Have you ever had to extract data from a Microsoft SQL Server database and export it to an Excel file, but did not want to use a DTS package? The concept in this article is not new, but it is the answer and will also work on 64-bit SQL boxes.   …
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

743 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

13 Experts available now in Live!

Get 1:1 Help Now