Solved

SQL query in SSRS

Posted on 2009-04-08
15
628 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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 500 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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

695 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