Home » SQL & PL/SQL » SQL & PL/SQL » Date filter leads to different number of records to be displayed (10g)
Date filter leads to different number of records to be displayed [message #398095] Wed, 15 April 2009 04:15 Go to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi,

I have two queries

QUERY1
SELECT lines."cust", lines.ord_date, order.rep, SUM(lines.price)
FROM lines , order
WHERE ( ( order."korder" = lines."korder" ) ) AND ( lines.ord_date >= TO_DATE('20080929000000','YYYYMMDDHH24MISS') ) 
GROUP BY lines."cust", lines.ord_date, order.rep;


QUERY2
SELECT lines."cust", lines.ord_date, order.rep, SUM(lines.price)
FROM lines , order
WHERE ( ( order."korder" = lines."korder" ) ) 
AND ( lines.ord_date >= TO_DATE('20080929000000','YYYYMMDDHH24MISS') AND lines.ord_date <= TO_DATE('20080930000000','YYYYMMDDHH24MISS') )
GROUP BY lines."cust", lines.ord_date, order.rep


Both query does same thing but filtered based on different dates.

Query 1 -> gives
( lines.ord_date >= TO_DATE('20080929000000','YYYYMMDDHH24MISS') ) 



Query 2 -> gives
( lines.ord_date >= TO_DATE('20080929000000','YYYYMMDDHH24MISS') AND lines.ord_date <= TO_DATE('20080930000000','YYYYMMDDHH24MISS') 


The question is , why when i try to compare the results returned by Query 1 in terms of record count againt query 2, im getting different count.

For instance, when i take those records returned by Query 1, in excel, and do a count for those records having ord_date more or equal to 29-Sep-08 and less an equal to 30-Sep-08, im not getting the same count retured by Query 2, Any help will be much appreciated
Re: Date filter leads to different number of records to be displayed [message #398097 is a reply to message #398095] Wed, 15 April 2009 04:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd guess that either the format you're selecting the dates in, or Excel is stripping the time component off the dates selected, and that's throwing your counts out.
Re: Date filter leads to different number of records to be displayed [message #398106 is a reply to message #398097] Wed, 15 April 2009 04:39 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi,

Tq, but can you eloborate more on the following
I'd guess that either the format you're selecting the dates in


Do you mean the format specified in to_date, is there any way i could correct it?
Re: Date filter leads to different number of records to be displayed [message #398111 is a reply to message #398095] Wed, 15 April 2009 04:51 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's not the to_date that's the problem.
It's whether or not the dates in excel have a time component.

If you have a record with an ord_date of 30-09-2008 12:31:00 then query two will ignore it.
But if when you dump that data into excel the time component gets stripped off - well then excel is going to count it as being <= 30-Sep-08
Re: Date filter leads to different number of records to be displayed [message #398124 is a reply to message #398111] Wed, 15 April 2009 05:35 Go to previous message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Excellent, that solved the issue.

Thank you guys.
Previous Topic: Help with Data Types of Different versions of Oracle
Next Topic: Can we make it in a single query?
Goto Forum:
  


Current Time: Tue Dec 06 15:51:10 CST 2016

Total time taken to generate the page: 0.15212 seconds