Solved

SSRS  2005 does not filter dates correctly

Posted on 2011-09-13
2
321 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
[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
2 Comments
 
LVL 29

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

After much searching across the internet I have found that you could not set the name of the file you were attaching to dynamic report subscriptons in Microsoft Reporting Services. I did manage to find one article showing you how your could make a s…
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.   …
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

696 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