Cannot convert String to date in Oracle 11g

Question

I'm trying to insert Date into table from user input but something doesn't work.

I have the following Query :

    INSERT INTO DOCTORS.TREATMENTS 
    (START_OF_TREATMENT, END_OF_TREATMENT, DOCTORS_ID,PACIENTS_ID, DIAGNOSIS_ID) 
    VALUES (TO_DATE(&startdate, 'yyyy/mm/dd'), TO_DATE(&enddate, 'yyyy/mm/dd'), 3, 1, 1);

For start date I set :

2000/10/01

And for end date I set :

2000/11/01

It seem ok for me but I've got the following error :

Error report - ORA-01858: a non-numeric character was found where a numeric was expected ORA-06512: at "SYS.STANDARD", line 167 ORA-06512: at line 2 01858. 00000 - "a non-numeric character was found where a numeric was expected" *Cause: The input data to be converted using a date format model was incorrect. The input data did not contain a number where a number was required by the format model. *Action: Fix the input data or the date format model to make sure the elements match in number and type. Then retry the operation.

Any one can explain to me why this error occurred.

Best regards, Petar.

Are you sure THAT is the error message you get? I tried exactly that expression (without the missing single-quotes, see Bob's answer) and I get a different error message, ORA-01840, Input value not long enough for date format. And indeed, that is the message I expected, because - as @Bohemian says - Oracle will first substitute the number 200 for the expression you gave it. Bob gave you the right way to correct your code, but why you got the error YOU did is still unclear. — user5683823
– user5683823, Commented Dec 18, 2016 at 13:55
I'm sure. I receive ORA-01840 but that was when I tried to give date like this 1/1/2005. When I fix the query as Bob's suggestion it works perfect. — Petar Petrov
– Petar Petrov, Commented Dec 18, 2016 at 14:43

Bob Jarvis - Слава Україні · Accepted Answer · 2016-12-18 13:26:37Z

3

The value of the first parameter passed to TO_DATE must be a string. After substitution with the value you've given your code will look like

TO_DATE(2000/10/01, 'yyyy/mm/dd')

which fails as shown.

The solution is to put the parameter usage in single-quotes to make the substituted value a string, as in

TO_DATE('&startdate', 'yyyy/mm/dd')

This way, when &startdate is substituted you'll get

TO_DATE('2000/10/01', 'yyyy/mm/dd')

which will work as expected.

Do the same for &enddate.

Best of luck.

edited Dec 18, 2016 at 13:26

answered Dec 18, 2016 at 13:24

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user5683823 Over a year ago

It doesn't fall "as shown" (at least on my machine). On my system I get a different error message if the substitution variable is not enclosed in single quotes - see Comment under the original post. While this is the right way to fix the code, it doesn't fully explain why the OP got the specific error message he did.

Collectives™ on Stack Overflow

Cannot convert String to date in Oracle 11g

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related