Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL query in SSRS

Posted on 2009-04-08
15
Medium Priority
?
631 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

604 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