What is the True Default Date Format in Oracle and How Does it Work?

The truth about Oracle database date format and its operating mechanism

Many articles claim that Oracle's default date format is YYYYMMDD, DD/MM/YYYY or YYYY-MM-DD. However, this misunderstanding stems from the fact that Oracle itself does not have a predefined date format. Oracle internally stores dates as binary values ​​that represent year, month, day, hour, minute, and second.

When inserting a date into Oracle using a string such as '25-JAN-18', Oracle attempts to convert the string to a date based on the NLS_DATE_FORMAT session parameter. The value of NLS_DATE_FORMAT will vary based on the user's region and session settings. Therefore, valid date formats can vary between users and can change dynamically within a session.

String to date conversion

To avoid relying on implicit conversions, it is recommended to explicitly convert a string to a date using one of the following methods:

• Date literal: Use the DATE keyword followed by a valid date representation, such as DATE '2018-01-25'.
• Timestamp literal: Use the TIMESTAMP keyword followed by the complete datetime, including the time and fractional seconds, for example TIMESTAMP '2018-01-25 01:23:45'.
• TO_DATE function: Use TO_DATE('date_string', 'format_string') to convert a string into a date according to the specified format, such as TO_DATE('25-JUN-18', 'DD-MON- RR').

Change NLS_DATE_FORMAT

To change the NLS_DATE_FORMAT session parameter, use the following statement:

<code class="language-sql">ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';</code>

Default NLS_DATE_FORMAT

The default NLS_DATE_FORMAT depends on the NLS_TERRITORY session parameter, which reflects the user's locale. The following query demonstrates the relationship between NLS_TERRITORY and NLS_DATE_FORMAT:

<code class="language-sql">...
SELECT CAST(f.format AS VARCHAR2(12)) AS format,
       LISTAGG(t.territory, ', ') WITHIN GROUP (ORDER BY t.territory) AS territories
...</code>

Running this query will generate a list of NLS_DATE_FORMAT values ​​and their corresponding applicable regions.

The above is the detailed content of What is the True Default Date Format in Oracle and How Does it Work?. For more information, please follow other related articles on the PHP Chinese website!