Change the date output format (nls_date_format)

Changing the date output format (nls_date_format) in sqlplus)

In the work, we encounter the use of spool to export data, where there is a date format field, because the format of each column, you do not want to use to_char to format the date, in addition, if to_char is used, all columns must be written in the SELECT statement .....................

In the work, we encounter the use of spool to export data, where there is a date format field, because the format of each column, you do not want to use to_char to format the date, in addition, if to_char is used, all columns must be written in the SELECT statement. In win, the default format is dd-mm-yy, as follows:
SQL> select sysdate from dual;

Sysdate
----------
Month 21-12-04

The format of the date field in the database is yyyy-mm-dd hh24miss. The exported data must also be in this format. You can set nls_date_format as follows:
1. Use alter session to modify
SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24: MI: ss ';
SQL> select sysdate from dual;

Sysdate
-------------------
2004-12-21 14:44:24

2. Set nls_date_format iN OS
Open a console window
C:> set nls_date_format = YYYY-MM-DD hh24: MI: SS // Note: here the YYYY-MM-DD hh24: MI: SS cannot be enclosed by quotation marks, but must be added to Unix systems

SQL> select sysdate from dual;

Sysdate
-------------------
2004-12-21 14:45:44

After this change is only valid for the current Console window, if you do not want to set every time, modify the system/user environment variables, add a new nls_date_format variable, value for YYYY-MM-DD hh24: MI: SS
Open a console window
SQL> select sysdate from dual;

Sysdate
-------------------
2004-12-21 14:46:15

In this way, select * From tabs in spool.