Oracle NLS_DATE_FORMAT, nls_date_format

Oracle NLS_DATE_FORMAT, nls_date_format

Nls_date_format is used to modify the date format.

• Modify the session level: alter session set nls_date_format = 'yyyy-mm-dd hh24: mi: ss'
• Modify the Registry in windows: HKEY_LOCAL_MACHINE.SOFTWARE.ORACLE.HOME0 new string value NLS_DATE_FORMAT and set its value to: YYYY-MM-DD: HH24: MI: SS
• UNIX/LINUX: export NLS_DATE_FORMAT = 'yyyy-MM-DD HH24: MI: ss'

  Note: Sometimes you also need to set NLS_LANG: export NLS_LANG = AMERICAN, which may be related to Shell/platform.

Note:Security Questions: Nls_date_format does not limit the character format or can even be set to an SQL statement. This is an SQL query that does not use the preparestatement method, it is very dangerous (You may not realize this usage, especially in the stored procedure, you think the type is date, it will not be a problem ).

Therefore, to ensure security, you must always prioritize variable binding (in addition to security factors, performance will be greatly improved )!

Oracle nls_date_format
In oracle, what is the problem of alter session set nls_date_format?

Session is the basic information related to the connection to the database.

Alter session set nls_date_format = 'yyyy-dd -- mm'

Is to modify the date display format of this session.

This modification only modifies the logon status.
Do not modify other sessions.

If you log out, the information will be lost.
After you log on again, it is a new Session.

The following is an example of a test:

D: \> sqlplus test/test @ testdb
SQL * Plus: Release 9.2.0.1.0-Production on Monday April 1 09:46:43 2013
Copyright (c) 1982,200 2, Oracle Corporation. All rights reserved.
Connect:
Oracle Database 10g Express Edition Release 10.2.0.1.0-Production

-- Date of the first query
SQL> select sysdate from dual;
SYSDATE
----------
Month 1-4-13

-- Modify the date display format of a session
SQL> alter session set nls_date_format = 'yyyy-dd-mm'
2;
The session has been changed.

-- Query date

SQL> select sysdate from dual;
SYSDATE
----------
2013-01-04

-- Reconnect once.
SQL> conn test/test @ testdb
Connected.

-- Query the date again, and the Session settings are reset.
SQL> select sysdate from dual;
SYSDATE
----------
Month 1-4-13
SQL>