ORA-01843 not a valid month

[Michael Wyatt]

Greetings and Salutations!

I am a desktop support tech at an insurance company. We have about 20 developers that use PL-SQL on Windows XP Pro.

One of my users gets the error "ORA-01843: not a valid month" when he enters a date with the format MM/DD/YYYY. A previous temporary workaround was to have him use another format, but management says the queries must be standardized for coding purposes.

I checked in the registry for the NLS_DATE_FORMAT entry, and it is MM/DD/YYYY HH:24:MI:SS as it should be. (Actually, I found it in more than one place, but it was correct in each.) I have also confirmed that Regional and Language Options were set correctly. The settings in PL-SQL seem to be correct and match the other users, too.

Where else can I check for settings to correct this issue in PL-SQL?

It works fine for the other 19 users. Also, I had the user try his profile on a neighboring system and the issue did not follow him. I logged in to his system, and the error occurred with my profile as well. This issue seems to be machine specific.

Any help would be appreciated. I _really_ do not wish to re-image the system or set this user up on another system if I can avoid it.

~MDWyatt

 

[Bo Pedersen]

Try looking in Tools -> Preferences -> User Interface -> Date/time. There you can specify a user defined date format or whether to use the Windows format or Oracle format. That could be the difference.

Regards
Bo Pedersen

 

[Marco Kalter]

If you are using a literal date value in a SQL statement without using the to_date() function with a date format, then it must conform to the sessions NLS_DATE_FORMAT. To check the current format, run the following query:

select *
  from nls_session_parameters p
 where p.parameter = 'NLS_DATE_FORMAT';