How to change Oracle Database NLS DATE FORMAT

To change the NLS_DATE_FORMAT setting Use the below command, it will help to solve your problems for after restart database.

Also, if you do not want to do this at the instance level, you can make session NLS_DATE_FORMAT based on sql queries.

ALTER SESSION SET NLS_DATE_FORMAT=’DD-MM-YYYY’;

[oraetkb@sp000dba01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Apr 22 17:18:59 2020

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> select sysdate from dual;

SYSDATE

22-APR-20

SQL> alter system set nls_date_format=’dd.mm.yyyy hh24:mi:ss’ scope=both;
alter system set nls_date_format=’dd.mm.yyyy hh24:mi:ss’ scope=both
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this
option

SQL> alter system set nls_date_format=’dd.mm.yyyy hh24:mi:ss’ scope=spfile;

System altered.

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 1.2147E+10 bytes
Fixed Size 12172976 bytes
Variable Size 7660941648 bytes
Database Buffers 4395630592 bytes
Redo Buffers 77959168 bytes
Database mounted.
Database opened.

SQL> select sysdate from dual;

SYSDATE
22.04.2020 17:23:26

You can also look at the other posts.

Click for detailed information

Have a nice day.