oracle-databasesysdate

Can't permanently change SYSDATE Format

I want to change the date format of Oracle Now it's DD/MM/YYYY, i want to change it to MM/DD/YYYY

I used a simple code (thank you google)

ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI';
select sysdate from dual;

The problem is when I disconnect and reconnect, the changes are cancelled,

Any help ?

The main of the change is to let Oracle accept insert date with the format MM/DD/YYYY

Thanks in advance

Solution

  • You can set this format also in your Registry at

    HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_{Oracle Home Name}\NLS_DATE_FORMAT,

    resp.

    HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\KEY_{Oracle Home Name}\NLS_DATE_FORMAT

    Example to do this from command line:

    reg add "HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraClient11g_home1" /v NLS_DATE_FORMAT /t REG_SZ /d "MM/DD/YYYY HH24:MI" /f

    Another possibility is to set the value as Environment Variable, e.g.:

    set NLS_DATE_FORMAT="MM/DD/YYYY HH24:MI"

    Note, the Environment Variable takes precedence over Registry settings.

    But be aware, these just define the default value of NLS_DATE_FORMAT. It can be changed by ALTER SESSION SET NLS_DATE_FORMAT = ... command at any time.