Oracle Time Format Discussion Online A lot of Oracle new scholars is annoying to Oracle's time format, often designed a table with a time field, inserting data fails to the table. Recalling that when I first started learning Oracle, I couldn't touch this time format. Although it was a small problem, the masters were discussed, but for beginners, this is also a unclear hurdle, did not find If the gateway, it may not be able to go a few days. In fact, it is a window paper, it is not difficult. Here is a simple summary, because of the limited level, please give a master to supplement. 1 Oracle's data stored in the time format data stored in the Oracle database, is stored in Oracle-specific format, accounting for 7 bytes, regardless of the time format displayed when the query is displayed, what is the specificity of the specificity, I am not very clear, please give me a master. The time of storage includes the day and month, the minimum accuracy is second, and the time unit of the following is not stored. Therefore, you should pay attention to this program (such as PB client programs) to support milliseconds (such as PB client programs). The time format displayed when the query is determined by the session environment, or is defined by the user, regardless of the database.
2 Oracle Time Display Form Usually, after the client is connected to the database, Oracle will give a default time format data display, which is related to the character set used. Generally display the year and month without display time. For example, when using the US7ASCII character set (or other English character set), the default time format is displayed as: 28-JAN-2003, and the use of the ZHS16GBK character set (or other Chinese character set) time format is displayed as : 2003- January -28. When the data is inserted into the table, if the conversion function is not used, the format of the time field must follow the time format of the session environment, otherwise it cannot be inserted. Check the time format of the current session, you can use the following SQL statements: SQL> SELECT SYSDATE from DUAL;
3 Data inserting the time format in the Oracle table Insert the data inserted into the time format of the Oracle Table, is a relatively trouble of trouble, or you strictly follow your time value in the time format of the current session, or use the conversion function custom time Data format. There are two conversion functions related to time: to_CHAR and TO_DATE. TO_CHAR (Time Value, Time Format) Function Converts Time Value to string, usually used in the query; TO_DATE (string, time format), transform the format string, usually in the INSERT statement. In the date format, various date format components are not discussed in detail. Please check the use of these two functions in the Oracle help documentation, or check the PL / SQL programming book, speak very detailed.
4 How to modify the date format in the session Modifying the date format in the current session It is said that there are three ways, I only have the first two ways to test, modify the glogin.sql file, I have not tried it, I don't know if it is really Row. The following explanation. (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'; modify the current session time format to this format: 2003-01- 28 15:23:38, that is, four years - two months - two days, space, 24-hour hours: minutes: seconds. This modification method is only valid for the current session. Note that it is a current session instead of the current SQL * PLUS window. That is, after you modify this, use the connect command to connect to the database other by other users or connected to another database, the date format is invalid, and it is restored to the default date format. (2) Modify the registry (only on the Windows system) Add a string (8i version) in the registry / hkey_local_machine / software / oracle / home0 primary key, the string is named NLS_DATE_FORMAT, the value of the string is the time you want to define Format, such as YYYY-MM-DD HH24: MI: SS, then restart SQL * Plus. This modification method is valid for the SQL * PLUS window, that is, whether you open how many SQL * Plus windows, the default is this time format. Modify the server-side registry invalid, only the registry of the client is modified is valid. There is no such similar way under UNIX system, I don't know, I will not use UNIX or the Linux system. (3) Modify the glogin.sql file in the $ ORACLE_HOME / SQLPLUS / Admin directory I have not tried it, I don't dare to say, so as not to mislead everyone. Please understand the master of this method to advise. 5 Date Format Priority If in a specific environment, you have modified the registry, but use the alter session command to modify the current session, which modification is valid? The alter session command is valid, its priority is the highest. That is, no matter what the current client environment is like, the registry modifies what is like, as long as you use the alter session command to modify the time format, then you will be subject to the time format you modified. Therefore, if the SQL * Plus interactive query or inserts data in time format, both methods can be used. If you use the SQL file to make batch inserts or timing execution, it is best to use the Alter Session command before using time data to modify the session to ensure that the normal operation and environment of this SQL statement is independent. Similarly, if you need to use data in the client program in the front desk, it is best to modify the session before use, or use the to_Char or to_date function to convert (strongly recommended to use the conversion function) to ensure the program operation and environment Not related.
