SSRS 2005 does not filter dates correctly

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

rwheeler23Asked:
Who is Participating?
 
sammySeltzerConnect With a Mentor Commented:
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
 
rwheeler23Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.