Oracle Modify Default date format __oracle

1. Oracle Date format

The default time format data for Oracle data is displayed in relation to the character set being used. Usually displays the month and the year, but does not show time and seconds.

For example, when using the Us7ascii character set (or another English character set), the default time format is displayed as: 28-jan-2003,

When using the ZHS16GBK character set (or other Chinese character set), the time format defaults to: 2 March-January-28.

When inserting data into a table, if you do not use a transform function, the format of the time field must conform to the time format of the session environment, or it cannot be inserted.

To view the time format for the current session, you can use the following SQL statement:
Sql> select Sysdate from dual;

2. Methods to modify Oracle date format

(1) Modify the date format of the current session in Sql*plus
Sql> alter session Set Nls_date_format = ' Yyyy-mm-dd hh24:mi:ss ';
Modifies the current session's time format to this format: 2003-01-28 15:23:38, four-bit year-two-bit-two-day, space, 24-hour-per-hour: minutes: seconds.
This modification method is valid only for the current session.

Note that the current session is not the current Sql*plus window. That is, if you modify this and use the Connect command to connect to the database with another user or to a different database, the date format becomes invalid and reverts to the default date format.

(2) To modify the registry (Windows system only)
Add a string (8i version) to the registry/HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/HOME0 primary key, the string name is Nls_date_format, and the value of the string is the time format you want to define, such as: YYYY-MM-DD HH24:MI:SS, then reboot
Sql*plus.
This modification is valid for the Sql*plus window, which is the default time format, regardless of how many sql*plus windows you open. Modifying the server-side registry is invalid, and only modifying the client's registry is valid.

(3)underLinux : Oracle user Edit. Bash_profile Add the following
Export nls_date_format= ' Yyyy-mm-dd HH24:MI:SS '
Re-login to take effect

(4) Login with SYSDBA
Then update the fields in the props$ table.
Update props$ Set value = ' Yyyy-mm-dd HH24:MI:SS ' where parameter = ' Nls_date_format ';