ORA-01830: date format picture ends before converting

Problem Description:
-------------------------
SQL> create table test_t (a date, b timestamp) ;
Table created.

SQL> insert into test_t values(SYSDATE,SYSTIMESTAMP);
1 row created.

SQL> select * from test_t;
A B
--------- ---------------------------------------------------------------------------
10/JUN/08 10-JUN-08 03.59

SQL> select to_date(a,'DD-MON'), to_timestamp(b,'DD-MON-RR') from test_t;
select to_date(a,'DD-MON'), to_timestamp(b,'DD-MON-RR') from test_t
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string


Cause of The Problem:
---------------------------------
We can't use here TO_DATE or TO_TIMESTAMP function here to define or display date as our wish is to omit any value. If we want to display date or timestamp value then the format must be valid as of default format in database _properties. If we want to omit any portion or add any then the date becomes invalid, hence error produces. So if we want to display as our wish we must have to use TO_CHAR conversion. Also we can set NLS_DATE_FORMAT or NLS_TIMESTAMP_FORMAT by using ALTER SYSTEM.

If you want to use TO_DATE and TO_TIMESTAMP then we have to use FULL format of default date settings.

Solution of The Problem:
---------------------------------
1)Using a TO_CHAR conversion.

SQL> select to_char(a,'DD-MON'), to_char(b,'DD-MON-RR') from test_t;

TO_CHA TO_CHAR(B
------ ---------
10-JUN 10-JUN-08

2)Setting NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT by ALTER SESSION.

SQL> alter session set NLS_TIMESTAMP_FORMAT='DD-MM-YY';
Session altered.

SQL> alter session set NLS_DATE_FORMAT='DD-MM';
Session altered.

SQL> select * from test_t;
A B
----- ---------------------------------------------------------------------------
10-06 10-06-08

3)Providing Valid Date and Timestamp valie.

If you want to use TO_DATE or TO_TIMESTAMP then you must give the valid date and timestamp settings. To know valid date and timestamp settings issue,

SQL> select property_name , property_value from database_properties where property_name='NLS_DATE_FORMAT' or property_name='NLS_TIMESTAMP_FORMAT';

PROPERTY_NAME PROPERTY_VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT DD-MON-RR
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM

Reset any value,
SQL>exit;
SQL> select to_date(a,'DD-MON-RR'), to_timestamp(b,'DD-MON-RR HH.MI.SSXFF AM') timstamp from test_t;

TO_DATE(A TIMSTAMP
--------- ---------------------------------------------------------------------------
10-JUN-08 10-JUN-08 03.59.33.274624 AM


In RMAN you can get the error like below,
RMAN> run{
.
.
SET UNTIL TIME '06-JUN-08 15:15:00';
.
.
}
executing command: SET until clause
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of set command at 09/08/2008 02:40:52
ORA-01830: date format picture ends before converting entire input string

To solution is to use TO_DATE conversion like,
RMAN>run{
.
.
SET UNTIL TIME "TO_DATE('06-JUN-08 15:15:00','DD-MON-YY HH24:MI:SS')";
.
.
}
Related Documents
http://arjudba.blogspot.com/2009/12/oracle-object-type-exercises-varray.html
http://arjudba.blogspot.com/2009/12/practice-oracle-joins-examples.html
http://arjudba.blogspot.com/2009/12/oracle-security-practices.html
http://arjudba.blogspot.com/2009/12/exercises-with-oracle-create-table-add.html
http://arjudba.blogspot.com/2009/12/oracle-database-creation-exercises.html
http://arjudba.blogspot.com/2009/12/basic-oracle-sql-exercise.html
http://arjudba.blogspot.com/2009/08/format-model-modifiers-fx-and-fm.html
http://arjudba.blogspot.com/2009/08/number-format-models-in-oracle.html
http://arjudba.blogspot.com/2009/08/format-models-in-oracle.html
http://arjudba.blogspot.com/2009/07/sql-decode.html
http://arjudba.blogspot.com/2009/07/how-to-know-row-of-table-belong-to.html
http://arjudba.blogspot.com/2009/06/how-to-know-which-objects-are-being.html
http://arjudba.blogspot.com/2009/06/ddl-with-wait-option-in-11g.html
http://arjudba.blogspot.com/2009/06/ora-00939-too-many-arguments-when-case.html
http://arjudba.blogspot.com/2009/03/oracle-datatype-internal-code.html
http://arjudba.blogspot.com/2009/03/how-to-know-list-of-constraints-and.html
http://arjudba.blogspot.com/2009/02/how-to-know-dependent-objectswhich.html
http://arjudba.blogspot.com/2009/02/how-to-search-stringkey-value-from.html
http://arjudba.blogspot.com/2009/02/how-to-know-when-tableobjects-ddlcode.html
http://arjudba.blogspot.com/2009/02/ora-00920-invalid-relational-operator.html
http://arjudba.blogspot.com/2009/01/adding-default-value-to-column-on-table.html
http://arjudba.blogspot.com/2009/01/ora-12838-cannot-readmodify-object.html
http://arjudba.blogspot.com/2009/01/ora-01779-cannot-modify-column-which.html
http://arjudba.blogspot.com/2009/01/updating-table-based-on-another-table.html
http://arjudba.blogspot.com/2009/01/ora-00054-resource-busy-and-acquire.html
http://arjudba.blogspot.com/2008/12/troubleshoot-ora-02292-ora-02449-and.html
http://arjudba.blogspot.com/2008/06/ora-00903-oracle-database-reserved.html
http://arjudba.blogspot.com/2008/06/hints-in-oracle.html
http://arjudba.blogspot.com/2008/06/examples-of-usage-of-composite-index.html
http://arjudba.blogspot.com/2008/06/find-indexes-and-assigned-columns-for.html
http://arjudba.blogspot.com/2008/06/reasons-for-using-alter-table-statement.html
http://arjudba.blogspot.com/2008/06/alter-table-rename-table-add-column.html
http://arjudba.blogspot.com/2008/06/ora-01830-date-format-picture-ends.html
http://arjudba.blogspot.com/2008/06/default-date-timestamp-and-timestamp.html
http://arjudba.blogspot.com/2008/06/create-temporary-table-in-oracle.html
http://arjudba.blogspot.com/2008/06/example-of-antijoin-semijoin-curtesian.html
http://arjudba.blogspot.com/2008/12/ora-02297-cannot-disable-constraint.html
http://arjudba.blogspot.com/2008/10/convert-decimal-to-hexadecimal-on.html
http://arjudba.blogspot.com/2008/10/how-to-generate-fibonacci-series-in.html
http://arjudba.blogspot.com/2008/10/same-sounded-words-in-oracle.html
http://arjudba.blogspot.com/2008/09/type-of-constraint-in-oracle.html
http://arjudba.blogspot.com/2008/09/how-to-move-lob-data-to-another.html
http://arjudba.blogspot.com/2008/08/subqueries-in-oracle-with-example.html
http://arjudba.blogspot.com/2008/08/how-to-monitor-alert-log-file-in-oracle.html
http://arjudba.blogspot.com/2008/08/solution-of-ora-01873-leading-precision.html
http://arjudba.blogspot.com/2008/07/literals-and-literal-types-in-oracle.html
http://arjudba.blogspot.com/2008/07/ora-01722-invalid-number.html
http://arjudba.blogspot.com/2008/07/ora-00936-missing-expression.html
http://arjudba.blogspot.com/2008/07/ora-01756-quoted-string-not-properly.html
http://arjudba.blogspot.com/2008/07/pls-00428-into-clause-is-expected-in.html
http://arjudba.blogspot.com/2008/07/schema-object-naming-rules.html
http://arjudba.blogspot.com/2008/06/datetime-and-interval-datatypes.html
http://arjudba.blogspot.com/2008/06/large-object-lob-datatypes-with-example.html
http://arjudba.blogspot.com/2008/06/history-of-sql.html
http://arjudba.blogspot.com/2008/06/what-is-sql.html