Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Wrong results from date comaprisons

RE: Wrong results from date comaprisons

From: Ken Naim <kennaim_at_gmail.com>
Date: Tue, 24 May 2005 12:56:30 -0500
Message-ID: <42936acf.1c23deb8.1df4.2a8f@mx.gmail.com> 





 I exported the table and imported it to a different schema, and the query
worked as it should against the new table. I then rebuilt the old table and
it works fine now as well. The table structure is 



CREATE TABLE TS.AGENCY_HISTORY



(

  LGL_ENTY_ID                    NUMBER         NOT NULL,
  LGL_ENTY_ROLE_TYP_CD           VARCHAR2(15 BYTE) NOT NULL,
  AGY_HIST_NO                    NUMBER         NOT NULL,
  AGY_TYP_CD                     VARCHAR2(15 BYTE),
  AGY_HIST_EFF_DT                DATE           NOT NULL,
  AGY_HIST_END_DT                DATE,
  AGY_HIST_CNTC_NM               VARCHAR2(40 BYTE),
  AGY_HIST_REF_NO                VARCHAR2(30 BYTE),
  COMS_SCH_TYP_CD                VARCHAR2(15 BYTE),
  AGY_HIST_COMS_SCH_OVRD_RT      NUMBER,
  LA_AGY_HIST_ERR_COV_EXPR_DT    DATE,
  NOTE_ID                        NUMBER,
  EMPL_ID_CREA                   NUMBER         NOT NULL,
  AGY_HIST_CREA_DTM              DATE           NOT NULL,
  EMPL_ID_UPDT                   NUMBER,
  AGY_HIST_UPDT_DTM              DATE,
  AGY_HIST_VOID_IND              CHAR(1 BYTE)   DEFAULT 'n'


NOT NULL,



  LA_AGY_HIST_PRC_BIND_AUTH_IND  CHAR(1 BYTE)   DEFAULT 'n'
NOT NULL



)


TABLESPACE TS



LOGGING 



COMPRESS 



NOCACHE



NOPARALLEL



MONITORING;





-----Original Message-----



From: Vlado Barun [mailto:vlado_at_cadre5.com] 
Sent: Tuesday, May 24, 2005 12:07 PM


To: 'Ken Naim'; 'Igor Neyman'; oracle-l_at_freelists.org
Subject: RE: Wrong results from date comaprisons



Please provide the structure of the table...



Vlado Barun, M.Sc.


Senior Data Architect, Cadre5


www.cadre5.com

Office: 865 690 4442
Mobile: 865 335 7652
e-mail: vlado_at_cadre5.com




AIM: vbarun2



-----Original Message-----



From: Ken Naim [mailto:kennaim_at_gmail.com]
Sent: Tuesday, May 24, 2005 12:40 PM


To: 'Igor Neyman'; 'Vlado Barun'; oracle-l_at_freelists.org
Subject: RE: Wrong results from date comaprisons



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_at_perceptron.com]
Sent: Tuesday, May 24, 2005 11:07 AM


To: kennaim_at_gmail.com; 'Vlado Barun'; oracle-l_at_freelists.org
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_at_perceptron.com





-----Original Message-----



From: oracle-l-bounce_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ken Naim
Sent: Tuesday, May 24, 2005 10:47 AM


To: 'Vlado Barun'; oracle-l_at_freelists.org
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_at_cadre5.com]
Sent: Tuesday, May 24, 2005 10:38 AM


To: kennaim_at_gmail.com; oracle-l_at_freelists.org
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_at_cadre5.com




AIM: vbarun2


-----Original Message-----



From: oracle-l-bounce_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org]


On Behalf Of Ken Naim


Sent: Tuesday, May 24, 2005 11:31 AM


To: oracle-l_at_freelists.org


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





--



http://www.freelists.org/webpage/oracle-l





--



http://www.freelists.org/webpage/oracle-l







--



http://www.freelists.org/webpage/oracle-l
Received on Tue May 24 2005 - 14:01:19 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US