Wrong results from date comaprisons
- From: "Ken Naim" <kennaim@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 24 May 2005 10:30:43 -0500
Has anyone experienced this with 10g (10.1.0.4.0)? I am getting wrong
results from the following query. The start and end dates are equal yet,
they are returned by the query and the difference column shows again that
they should be equal although there are many rows that are filtered
correctly. There aren't any indexes on the fields in questions and there is
a constraint on the start date field
select to_char(agy_hist_eff_dt,'DD-Mon-YY HH24:mi:ss')
s,to_char(agy_hist_end_dt,'DD-Mon-YY HH24:mi:ss') e,
agy_hist_end_dt-agy_hist_eff_dt d
from agency_history a
where agy_hist_void_ind='y'
and agy_hist_eff_dt!=agy_hist_end_dt
S E D
01-Jan-2004 00:00:00 01-Jan-2004 00:00:00 0
13-Oct-2003 00:00:00 13-Oct-2003 00:00:00 0
13-Jul-2004 00:00:00 13-Jul-2004 00:00:00 0
21-Oct-2004 00:00:00 21-Oct-2004 00:00:00 0
01-Jan-2005 00:00:00 01-Jan-2005 00:00:00 0
01-Jan-2004 00:00:00 01-Jan-2004 00:00:00 0
01-Jan-2003 00:00:00 01-Jan-2003 00:00:00 0
22-Jul-1999 00:00:00 22-Jul-1999 00:00:00 0
05-Jan-1998 00:00:00 05-Jan-1998 00:00:00 0
01-Jan-2004 00:00:00 01-Jan-2004 00:00:00 0
07-Jan-2004 00:00:00 07-Jan-2004 00:00:00 0
10-Jun-2004 00:00:00 10-Jun-2004 00:00:00 0
09-Jun-2004 00:00:00 09-Jun-2004 00:00:00 0
09-Nov-2004 00:00:00 09-Nov-2004 00:00:00 0
01-Jan-2005 00:00:00 01-Jan-2005 00:00:00 0
01-Jan-2003 00:00:00 01-Jan-2003 00:00:00 0
01-Jan-2005 00:00:00 01-Jan-2005 00:00:00 0
28-Jun-2002 00:00:00 28-Jun-2002 00:00:00 0
01-Jun-2001 00:00:00 01-Jun-2001 00:00:00 0
01-Jan-2003 00:00:00 01-Jan-2003 00:00:00 0
03-Apr-2000 00:00:00 03-Apr-2000 00:00:00 0
01-Apr-1999 00:00:00 01-Apr-1999 00:00:00 0
28-May-2002 00:00:00 28-May-2002 00:00:00 0
01-Apr-1999 00:00:00 01-Apr-1999 00:00:00 0
01-Jan-2003 00:00:00 01-Jan-2003 00:00:00 0
05-Jan-1998 00:00:00 05-Jan-1998 00:00:00 0
01-Jan-2003 00:00:00 01-Jan-2003 00:00:00 0
01-Jun-2001 00:00:00 01-Jun-2001 00:00:00 0
03-Apr-2000 00:00:00 03-Apr-2000 00:00:00 0
01-Jun-2001 00:00:00 01-Jun-2001 00:00:00 0
01-Jun-2001 00:00:00 01-Jun-2001 00:00:00 0
28-Jun-2002 00:00:00 28-Jun-2002 00:00:00 0
01-Jan-2003 00:00:00 01-Jan-2003 00:00:00 0
22-Jun-1999 00:00:00 22-Jun-1999 00:00:00 0
02-Jul-1999 00:00:00 02-Jul-1999 00:00:00 0
18-Apr-2000 00:00:00 18-Apr-2000 00:00:00 0
--
//www.freelists.org/webpage/oracle-l
Other related posts:
