ORA-01843: not a valid month

Problem Description:
---------------------------

SQL> select a, to_timestamp(b,'DD-MON-RR HH.MI.SSXFF AM') from test_ts;
A TO_TIMESTAMP(B,'DD-MON-RRHH.MI.SSXFFAM')
----------- ----------------------------------------------------------------
1 10-JUN-08 03.21.33.106197 AM

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

SQL> select a, to_timestamp(b,'DD-MON-RR HH.MI.SSXFF AM') from test_ts;
select a, to_timestamp(b,'DD-MON-RR HH.MI.SSXFF AM') from test_ts
*
ERROR at line 1:
ORA-01843: not a valid month

Cause of The Problem:
-----------------------------
The settings of the date or timestamp does not match with the settings or current date or timestamp format.

To know current session settings issue,
SQL> select * from NLS_SESSION_PARAMETERS where PARAMETER='NLS_TIMESTAMP_FORMAT';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT DD-MM-YY HH

TO know current instance settings query,

SQL> select * from NLS_INSTANCE_PARAMETERS where PARAMETER='NLS_TIMESTAMP_FORMAT';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT

To know database settings,
SQL> select * from NLS_DATABASE_PARAMETERS where PARAMETER='NLS_TIMESTAMP_FORMAT';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM

To know currently affected parameter issue,
SQL> select * from V$NLS_PARAMETERS where PARAMETER='NLS_TIMESTAMP_FORMAT';
PARAMETER VALUE
---------------- -----------------------------------
NLS_TIMESTAMP_FORMAT DD-MM-YY HH

As this settings does not match with 'DD-MON-RR HH.MI.SSXFF AM' format so error comes.

Solution of The Problem:
---------------------------------
1)Use a format that match current settings. like,
SQL> select a, to_timestamp(b,'DD-MM-RR HH.MI.SSXFF AM') from test_ts;
A TO_TIMESTAMP(B,'DD-MM-RRHH.MI.SSXFFAM')
---------------------------------------------------------------------------
1 10-06-08 03

2)Or left it to default as ,
SQL> select a, to_timestamp(b) from test_ts;
A TO_TIMESTAMP(B)
---------- ---------------------------------------------------------------------------
1 10-06-08 03

3)Or exit session if it is set session wise and issue same query.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
-bash-3.00$ sqlplus arju/a

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 10 04:51:05 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from V$NLS_PARAMETERS where PARAMETER='NLS_TIMESTAMP_FORMAT';

PARAMETER VALUE
-------------------- --------------------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM


SQL> select a, to_timestamp(b,'DD-MM-RR HH.MI.SSXFF AM') from test_ts;
A
----------
TO_TIMESTAMP(B,'DD-MM-RRHH.MI.SSXFFAM')
---------------------------------------------------------------------------
1
10-JUN-08 03.21.33.106197 AM


SQL> select a, to_timestamp(b,'DD-MON-RR HH.MI.SSXFF AM') from test_ts;
A
----------
TO_TIMESTAMP(B,'DD-MON-RRHH.MI.SSXFFAM')
---------------------------------------------------------------------------
1
10-JUN-08 03.21.33.106197 AM

Both version worked and returned the same formatted result.

SQL> select * from NLS_SESSION_PARAMETERS where PARAMETER='NLS_TIMESTAMP_FORMAT';

PARAMETER VALUE
-------------------- --------------------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM