Solved

SQL query in SSRS

Posted on 2009-04-08
15
610 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
  • 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now