Re: Help: To_date() problem in Pro*C ??

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Fri, 04 Jun 1999 17:56:24 GMT
Message-ID: <3759117c.17963830@inet16.us.oracle.com>

On Thu, 03 Jun 1999 20:42:26 -0700, Mirza Mohsin Beg <mbeg_at_netearnings.com> wrote:

>
>
>I have the following code (with NLS_DATE_FORMAT set as 'DD-MON-YYYY' in
>init.ora)
>
>{
>exec sql begin declare section
>localDateField char[25] = "03-JUN-1999 12:24:34";
>localSelectField char[5] = "";
>exec sql end declare section
>
>exec sql
>select 'x' into :localSelectField
>from dual
>where trunc(sysdate) = to_date(:localDateField, 'DD-MON-YYYY');
>
>}
>
>This gives me
>Ora-01830: date format picture ends before converting entire input
>string.
>What is going on ? What am I doing wrong?
>

Your bind value, localDateField, does not match your date mask. The bind value is in the format 'DD-MON-YYYY HH:MI:SS' and you are telling the database to convert that string using the format 'DD-MON-YYYY' which will not work.

eg. from sql*plus

SQL> desc a
 Name Null? Type

• -------- ---------------------------- D DATE

SQL> insert into a values ( to_date( '03-JUN-1999 12:24:34', 'DD-MON-YYYY') ); insert into a values ( to_date( '03-JUN-1999 12:24:34', 'DD-MON-YYYY') )

                                *

ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

Same error as you get in poc*c.

So, either change the localDateField value to not include a time component or extend the date format mask to match the actual format of the bind variable.

SQL> insert into a values ( to_date( '03-JUN-1999', 'DD-MON-YYYY') ); 1 row created.

SQL> insert into a

     values ( to_date( '03-JUN-1999 12:24:34', 'DD-MON-YYYY HH:MI:SS' )); 1 row created.

hope that helps.

chris.

>Thanks,
>
>-M
>ps: please email me directly also
>

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Jun 04 1999 - 12:56:24 CDT