ORA-01843: not a valid month

[Ales Vojtechovsk]

When I execute the query like

select *
from ids.r_exchange_rates t
where sexc_valuation_date='12.05.2011'

I get the error message:
ORA-01843: not a valid month

The option in Tools/Preferences/Date + User Defined DD.MM.YYYY has no effect.

If I execute
ALTER SESSION SET nls_date_format = 'DD.MM.YYYY'
then it works ok.

Can I set nls_date_format somwhere in Oracle Client Setting/Setup file or PL/SQL Developer?
Or can I let the ALTER SESSION command to be executed as first command for every session in PL/SQL Developer?

 

[Marco Kalter]

You can start regedit.exe and go to:

HKEY_LOCAL_MACHINE\Software\Oracle

On a 64 bit machine:

HKEY_LOCAL_MACHINE\Software\Wow6432Node\Oracle

Here you will find a KEY_ section, where is the name of your Oracle Home. In this section you can create the following string value:

NLS_DATE_FORMAT = DD.MM.YYYY

Now all Oracle client tools will use this setting.

 

[Ales Vojtechovsk]

Thank you Marco,

I resolved the problem in the meantime by setting NLS_DATE_FORMAT in Windows XP environment variables.

(and I do not have right to administer my regedit so I couldn't try you suggestion)

Regards,
Alex

 

[Rafael Zamora]

I have the same problem, date format configured in preferences did not aplly.

I tried to create a Key in regedit (HKEY_LOCAL_MACHINE\Software\Wow6432Node\Oracle\KEY_OraClient12Home1), but the problem persist.

I used this versions:
- PL/SQL Developer Version 12.0.7.1837 (64 bit)
- Windows 10 Build 17134.
- Oracle Database 12c Standar Edition Release 12.1.0.2.0

Thanks for your Help!!!

 

[fabrice]

I think this key (HKEY_LOCAL_MACHINE\Software\Wow6432Node\Oracle\KEY_OraClient12Home1) is for 32bits client on 64bits machine.
For a 64bits client, only HKEY_LOCAL_MACHINE\Software\Oracle\KEY_OraClient12Home1 must be use.

 

[Rafael Zamora]

Already exists!!

 

[Nicolas D.]

avoid making statement that expect a date format as per NLS Settings.
Prefer to use TO_DATE... or the literal: DATE 'YYYY-MM-DD'
- write DATE follow by string in 'YYYY-MM-DD' format.
that is universal, work regardless NLS Settings. A lot safer for scripting.