OraFAQ Forum: SQL & PL/SQL » Invalid data in Date fields!?!?!?

Home » SQL & PL/SQL » SQL & PL/SQL » Invalid data in Date fields!?!?!?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Invalid data in Date fields!?!?!? [message #19970]

Mon, 22 April 2002 06:04

Miguel A. Campos
Messages: 1
Registered: April 2002

Junior Member

Oracle 7.3.4 running over SCO Openserver.

Selecting a DATE field works ok:

select fec from dnis; <-- This works ok.

select months_between(sysdate,fec) from dnis; <-- This raises an ORA-01858: Non-numeric character found ("fec" is a date field!!!)

But:

select months_between(sysdate,to_date(to_char(fec,'ddmmyyyy'),'ddmmyyyy')) from dnis <-- Works OK!!

Could there possibly be invalid data in the date field. How can one reach to this situation? How can one insert invalid dates into a date field? Well, data seems to be OK, as I can transform the date field into char and then back into date and the query works. Maybe that is not the problem?

I cannot understand a thing. Could someone explain??

Thanks in advance

Miguel A. Campos

P.S.: Here is the table creation script, just in case someone doubts about the rest of the table:

CREATE TABLE DNIS (
DNI VARCHAR2 (9) NOT NULL,
AP1 VARCHAR2 (12),
AP2 VARCHAR2 (12),
NOM VARCHAR2 (12),
FEC DATE,
CONSTRAINT PK_DNIS
PRIMARY KEY ( DNI )
USING INDEX
TABLESPACE PNCINDEX PCTFREE 10
STORAGE ( INITIAL 3010560 NEXT 452608 PCTINCREASE 50 ))
TABLESPACE PNCDATOS
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 7260160
NEXT 1454080
PCTINCREASE 50
MINEXTENTS 1
MAXEXTENTS 99
FREELISTS 1 FREELIST GROUPS 1 )
NOCACHE;

CREATE INDEX DNIS2 ON
"EPNC".DNIS(AP1, AP2, NOM)
TABLESPACE PNCINDEX PCTFREE 10 STORAGE(INITIAL 6922240 NEXT 931840 PCTINCREASE 50 )
;

Report message to a moderator

Re: Invalid data in Date fields!?!?!? [message #19975 is a reply to message #19970]

Mon, 22 April 2002 09:09

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

did u check the date format?
the default format is
22-APR-02 or dd-mon-yy.
there is possibility for a mismatch in date format.

Report message to a moderator

Re: Invalid data in Date fields!?!?!? [message #19990 is a reply to message #19970]

Tue, 23 April 2002 01:26

John R
Messages: 156
Registered: March 2000

Senior Member

Tricky.

Does "SELECT to_char(fec,'dd-mon-yyyy') from dnis" work?

My best suggestion would be to write a quick pl/Sql procedure to step throught the records and when it finds one where the months_between raises an exception output the details.

Report message to a moderator

Previous Topic:	Where 1 = 2
Next Topic:	To get department name

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 18:19:50 CDT 2024