How to change the default NLS_DATE_FORMAT (Date format) in Oracle 10g/11g

It is really really easy to change the default NLS_DATE_FORMAT setting but to be honest, you should set it at a session level IMHO.

We basically just need to run “ALTER SYSTEM SET NLS_DATE_FORMAT=’YYYY-MM-DD’ SCOPE=SPFILE” as a user with sysdba privileges. If you started the Oracle instance without a spfile (it should be located at $ORACLE_HOME/dbs/spfile[instance name].ora), you will receive the ORA-32001 error.

ALTER SYSTEM SET NLS_DATE_FORMAT='YYYY-MM-DD' SCOPE=SPFILE;
ALTER SYSTEM SET NLS_DATE_FORMAT='YYYY-MM-DD' SCOPE=SPFILE
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup

Just create a new spfile, restart:

select INSTANCE_NAME from v$instance;
INSTANCE_NAME
----------------
UAT2
create spfile='/oracle/10g/dbs/spfileUAT2.ora' from pfile='/oracle/10g/dbs/initUAT2.ora';

*restart*

select INSTANCE_NAME from v$instance;
INSTANCE_NAME
----------------
UAT2
ALTER SYSTEM SET NLS_DATE_FORMAT='YYYY-MM-DD' SCOPE=SPFILE;
System altered.

*restart*

SELECT value FROM v$nls_parameters WHERE parameter ='NLS_DATE_FORMAT';
VALUE
----------------------------------------------------------------
YYYY-MM-DD

That’s it. 🙂

Share Button

6 Replies to “How to change the default NLS_DATE_FORMAT (Date format) in Oracle 10g/11g”

  1. Dear writer,
    Your article is greatly appreciated, but you know something.
    I want to know why I am getting a reversed NLS_DATE_FORMAT when I call a report from a form runtime? I mean even though the format mask is defined as ‘yyyy/mm/dd’; I am still getting weird numbers when the report is run.
    Waiting for a reply, and please don’t ignore my question because I need an answer as soon as possible.

    Sincerely,
    AB

  2. Thank you very much for this information. You really save my life. With this information you save me many hours of work!!

    Bye =)

  3. The best way to solve this issue is by defining a string value with the name:
    “NLS_DATE_FORMAT” and setting its value equal to RRRR/MM/DD.
    So your client will display the date according to the format you want…

    Thanks a lot.

Leave a Reply

Your email address will not be published. Required fields are marked *