Out of the blue, I just started getting this obnoxious exception. Why?? Why meeeeeee!!
Another weird thing is that is was only from my test server. Again Why?>>> Why Meeee!!!
In general when I executed some stored procedures or performed select staments againts the
Oracle database I just got that exception.
ORA-01843: not a valid month
CAUSE:
The source of the problem is that when a table has a date stored in a format that the client is not able
to understand due to format differentes it will throw this message.
For example, if the table is storing dates like ‘DD-MM-YY’, (that is day-month-year european format)
and the client tries to read the date as ‘MM-DD-YY’ or viceversa. In this case we can see that the
month number is confused with the day and it is very likely that there will be dates with a month value
bigger that 12.
SOLUTION:
The solution to this problem is to tell the client the right format to use for dates. This is part of the session values
in oralce. Part of those values can be queries with the following statement:
select * from nls_session_parameters
In the query results, we can see the value of the NLS_DATE_FORMAT that is being used by the client.
You can take several actions.
1. If you are just performing a query on SQL PLUS and is just an isolated query you can just change that value for
your current session by executing:
alter session set NLS_DATE_FORMAT=’DD-MM-RR’
This will take effect ONLY for the actual session. In this example we can see that the format is being changed to
‘day-month-year’. The RR, indicates the year.
2. If you want a more permanent change or at least something that applies to all your session. You can:
2.1. Create an After Logon Trigger something like:
CREATE OR REPLACE TRIGGER LOGINTRG AFTER LOGON ON DATABASE
BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''DD-MM-RR'''; END LOGINTRG;
2.2. Option 2. Go to the Registry and locate
\\HKEY_LOCAL_ MACHINE\SOFTWARE\ORACLE\KEY_HOMENAME\
For example in my case it was
\\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0
And create an String Key called NLS_DATE_FORMAT and set it to DD-MM-RR