You are correct, the statement I pasted was the first one I ran but then realized there could be a century difference so I reran it using the full year. The data was from the second statement pasted below. select to_char(agy_hist_eff_dt,'DD-Mon-YYYY HH24:mi:ss') s,to_char(agy_hist_end_dt,'DD-Mon-YYYY 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 -----Original Message----- From: Igor Neyman [mailto:ineyman@xxxxxxxxxxxxxx] Sent: Tuesday, May 24, 2005 11:07 AM To: kennaim@xxxxxxxxx; 'Vlado Barun'; oracle-l@xxxxxxxxxxxxx Subject: RE: Wrong results from date comaprisons Are you sure, that you are showing the exact statement you are running, or that your output produced by the statement you showed? to_char(agy_hist_eff_dt,'DD-Mon-YY HH24:mi:ss') should return something like: 01-Jan-04 00:00:00 and not: 01-Jan-2004 00:00:00 as shown in your output. Igor Neyman, OCP DBA ineyman@xxxxxxxxxxxxxx -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Ken Naim Sent: Tuesday, May 24, 2005 10:47 AM To: 'Vlado Barun'; oracle-l@xxxxxxxxxxxxx Subject: RE: Wrong results from date comaprisons The fields are date fields so AFAIK it cannot be any more precise. Also if that were the case the diff should return some tiny fraction -----Original Message----- From: Vlado Barun [mailto:vlado@xxxxxxxxxx] Sent: Tuesday, May 24, 2005 10:38 AM To: kennaim@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx Subject: RE: Wrong results from date comaprisons Depends on how precise the date columns are... Are you going down to the milliseconds? Your output only goes to the second... Vlado Barun, M.Sc. Senior Data Architect, Cadre5 www.cadre5.com Office: 865 690 4442 Mobile: 865 335 7652 e-mail: vlado@xxxxxxxxxx AIM: vbarun2 -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Ken Naim Sent: Tuesday, May 24, 2005 11:31 AM To: oracle-l@xxxxxxxxxxxxx Subject: Wrong results from date comaprisons 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 -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l