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

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

Comments

  1. AB says:

    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. Fernando says:

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

    Bye =)

  3. Alok says:

    This doesn’t seem to be working the same way in 11.2.0.3.

Leave a Reply

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

*
*

Facebook login by WP-FB-AutoConnect