ORA-01830: date format picture ends before converting entire input string

Oracle docs note this about ORA-01830 error:

       ORA-01830 date format picture ends before converting entire input string
 

Cause: A valid date format picture included extra data. The first part of the format picture was converted into a valid date, but the remaining data was not required.

Action: Check the specifications for date format pictures and correct the statement.

If using SQL*Loader, you can get the ORA-01839 error if the nls_date_format does not match the data:
05/06/2009 11:08:49 PM

alter session set nls_date_format='DD/MM/YYYY';
sqlldr . . .

ORA-01830 date format picture ends before converting entire input string

To avoid the ORA-01839 error, the statement was changed:

alter session nls_date_format='DD/MM/YYYY HH:MM:SS AM';

Share on Google Plus

About Mindsforest