?
Solved

SQL query in SSRS

Posted on 2009-04-08
15
Medium Priority
?
629 Views
Last Modified: 2012-05-06
I am  workign on a SSRS report. I created a report which gets the records between two date ranges. This report has 2 date parameters.  The  report is  running with out any errors but for some reason even i use date range the data is not filtered. I mean i am tryin to see the data between 1st of this month to current date. The results are showing records  before the startdate parameters. That mean the filter is not  working. But still it does not give any error.
Please see the code  below
select * from ABC
where date between @StartDate and @EndDate

Open in new window

0
Comment
Question by:svs1919
[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
  • 8
  • 7
15 Comments
 
LVL 5

Expert Comment

by:catherinelouise
ID: 24101947
Are the parameters you've created of a type DateTime, and is the 'Date' field from your table also a DateTime type?

Verify your query by hard-coding actual dates in there - when you know you are working with the right date type you can get your parameters to match.
0
 

Author Comment

by:svs1919
ID: 24101994
yes theya re  datetime Tpe. and i also tried to hard code the values int he query but still it dosent  filter
0
 
LVL 5

Expert Comment

by:catherinelouise
ID: 24102052
So the problem must be with querying the table rather than anything to do with the report parameters.  Are you able to share an example of data from your table?  Maybe we can figure out what the issue is from that.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:svs1919
ID: 24102084
This is one of the example from my data.
City               State           Company              Date                                  
FULTON        KY             KFC, LLC             2007-07-18 00:00:00.000
0
 
LVL 5

Expert Comment

by:catherinelouise
ID: 24102318
Great.  Now if you were to write a query something like select * from table where date between '18-jul-2007' and '19-jul-2007' do you get that record back?

Can you confirm the date format you are using in the where clause?
0
 

Author Comment

by:svs1919
ID: 24108012
when i  ran it  using the above  query i got following error
Msg 4145, Level 15, State 1, Line 3
An expression of non-boolean type specified in a context where a condition is expected, near 'date'.
0
 
LVL 5

Expert Comment

by:catherinelouise
ID: 24108410
Hmm, I'm a little confused - at the risk of asking the obvious, I'm going to verify anyway , you wrote the query as described below and you get the above error?  Can you also try the other query I mention below

---Verify Between---
SELECT * from TableName
WHERE Date between '18-Jul-2007' and '19-Jul-2007'
 
--Verify Greater Than
SELECT * from TableName
WHERE Date >= '18-Jul-2007'
AND Date < '19-Jul-2007'

Open in new window

0
 

Author Comment

by:svs1919
ID: 24108431
yep this one worked in the  query

SELECT * from TableName
WHERE Date >= '18-Jul-2007'
AND Date < '19-Jul-2007'
0
 
LVL 5

Expert Comment

by:catherinelouise
ID: 24108952
Well it seems like for whatever reason, the 'between' clause is not working - I'm afraid I'm not smart enough to tell you why if you are using SQL 2005 as described.

However I would be inclined to change the sproc as I have below which should function in exactly the same way as BETWEEN.
select * from ABC
where date >= @StartDate 
and date <= @EndDate

Open in new window

0
 

Author Comment

by:svs1919
ID: 24109366
yep i tried this but still it is  not  working
0
 

Author Comment

by:svs1919
ID: 24109551
i think the reason why its  causing this problem  is i am using a like operator and that  might  be the reason for the problem

select * from table
where date Between @startdate  and @endDate
and column like 'ABC%'
or Column like ' xyz%'
0
 
LVL 5

Expert Comment

by:catherinelouise
ID: 24109752
Again, you should just test the query with hardcoded values.  If BETWEEN wasn't working in your tests above, then the last query you posted probably isn't going to work either.  

In theory using LIKE shouldn't be a problem.  Adapting your query for example's sake using your data above, does this return you data?  
Select * from table
where Date >= '18-Jul-2007'
AND Date < '19-Jul-2007'
AND (Company like 'KFC%'
OR Company like 'XYZ%')
 
---Then to verify 'Between' clause
 Select * from table
where Date between '18-Jul-2007' AND '19-Jul-2007'
AND (Company like 'KFC%'
OR Company like XYZ%')

Open in new window

0
 
LVL 5

Accepted Solution

by:
catherinelouise earned 2000 total points
ID: 24109766
Sorry a comma was missing in that last snippet
Select * from table
where Date between '18-Jul-2007' AND '19-Jul-2007'
AND (Company like 'KFC%'
OR Company like 'XYZ%')

Open in new window

0
 

Author Comment

by:svs1919
ID: 24109786
ok cool its  working now thanks for  you help
0
 

Author Closing Comment

by:svs1919
ID: 31568275
Great Help
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Hi All, I am here to write a simple article to move SSRS (SQL Server Reporting Services) reports from one server to another. When I have faced the same issue to move reports those were developed by developer on development server and now need to …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

764 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