What's the Default Date Format in Oracle and Why is it Inconsistent?

What is the default date format in Oracle database?

Although the documentation specifies a variety of default date formats, there is actually no fixed format for Oracle dates. They are stored in a 7-byte internal format, representing the year (2 bytes), month, day, hour, minute, and second (1 byte each).

Why is there so much conflicting information?

This is due to the NLS_DATE_FORMAT session parameter. This parameter serves as the default format and will vary depending on the user's regional settings. Therefore, format inconsistencies can occur when users perform explicit string to date conversions or implicit conversions. Oracle, during implicit string to date conversion, attempts to interpret the string based on the NLS_DATE_FORMAT parameter, causing inconsistencies.

Recommended Practices

To avoid confusion, the following approach is recommended:

• Date literal: DATE '2018-01-25'
• Timestamp literal: TIMESTAMP '2018-01-25 01:23:45'
• Explicit TO_DATE conversion: TO_DATE('25-JUN-18', 'DD-MON-RR')

Modify NLS_DATE_FORMAT

To change the NLS_DATE_FORMAT in a session, use the following command:

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

Default value of NLS_DATE_FORMAT

The default value of NLS_DATE_FORMAT depends on the NLS_TERRITORY session parameter, which represents the user's locale. To see which formats are used in different regions, run the following query:

<code class="language-sql">SET SERVEROUTPUT ON;

VARIABLE cur REFCURSOR;

DECLARE
  territories SYS.ODCIVARCHAR2LIST;
  formats     SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
BEGIN
  SELECT value
  BULK COLLECT INTO territories
  FROM v$nls_valid_values
  WHERE parameter = 'TERRITORY'
  ORDER BY value;

  formats.EXTEND(territories.COUNT);
  FOR i IN 1 .. territories.COUNT LOOP
    EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TERRITORY='''||territories(i)||'''';

    SELECT value
    INTO formats(i)
    FROM NLS_SESSION_PARAMETERS
    WHERE PARAMETER = 'NLS_DATE_FORMAT';
  END LOOP;

  OPEN :cur FOR
  SELECT f.format AS format,
         LISTAGG(t.territory, ', ') WITHIN GROUP (ORDER BY t.territory) AS territories
  FROM (SELECT ROWNUM AS rn, COLUMN_VALUE AS territory FROM TABLE(territories)) t
         INNER JOIN
         (SELECT ROWNUM AS rn, COLUMN_VALUE AS format FROM TABLE(formats)) f
         ON (f.rn = t.rn)
  GROUP BY f.format;
END;
/

PRINT :cur;</code>

The above is the detailed content of What's the Default Date Format in Oracle and Why is it Inconsistent?. For more information, please follow other related articles on the PHP Chinese website!