Re: ORA-01830

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Fri, 27 Jul 2001 19:21:28 +0200
Message-ID: <996254490.12145.0.pluto.d4ee154e@news.demon.nl>

"Irfan Baig" <irfan_at_abstractedge.com> wrote in message news:20010727.112328.1494613810.30901_at_irf.local...
> Oracle 8.1.6 on Red Hat Linux 6.0.
> The SQL for the record below was generated from a db on the same oracle
> server version, except on an HP machine. I tried to add the record to my
> Linux server, but got the error below.
>
> Furthermore, if I remove the time segment from the SQL below
> (i.e. '28-SEP-2000 13:04:14' --> '28-SEP-2000'), the record
> gets added without any problem. The field TRAN_DATE is of type
> DATE.
>
> Would appreciate any help.
>
> --
> Connected to:
> Oracle8i Enterprise Edition Release 8.1.6.1.0 - Production
> With the Partitioning option
> JServer Release 8.1.6.0.0 - Production
>
> SQL> INSERT INTO COMPANY.CODE
> 2 (CODE, TRAN_DATE)
> 3 VALUES
> 4 (9516709,'28-SEP-2000 13:04:14')
> 5 /
> (9516709,'28-SEP-2000 13:04:14')
> *
> ERROR at line 4:
> ORA-01830: date format picture ends before converting entire input string

Your default date format is DD-MON-YYYY. For any other format you will need the to_date function in this case
to_date('28-SEP-2000 13:04:14','dd-mon-yyyy hh24:mi:ss')

I would recommend to use a to_date function *always*.

Hth,

Sybrand Bakker, Senior Oracle DBA Received on Fri Jul 27 2001 - 12:21:28 CDT