Solved

SQL query in SSRS

Posted on 2009-04-08
15
620 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

770 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