Oracle 的默认 DATE 格式

Question

第一次使用 Oracle SQL（我习惯了 MySQL）。我发现有关默认日期格式的信息相互矛盾。在多次尝试使用 TO_DATE 和我的 INSERT INTO my_table 语句后，我终于发现我正在使用的数据库需要 DD-MON-YY（即 25-JAN-18）。然而，在 stackoverflow 和其他地方的各个页面上，我看到一些说默认是 YYYYMMDD 或 DD/MM/YYYY 或 YYYY-MM-DD。为什么会有这么多相互矛盾的信息？

原文由 BigRedEO 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

Oracle 以及其他数据库允许您设置默认格式。开箱即用的格式（通常）是 DD-MON-RR，其中“RR”指的是两位数的年份。这是一种非常糟糕的格式，从模糊性（两位数的年份？）和国际化的角度来看（对于哪些国家来说，这实际上是默认的？）。但是甲骨文已经存在了很长时间。

标准格式也由国际标准化组织 ISO 定义。他们选择了更像 YYYY-MM-DD 的东西。实际上，连字符是可选的，但我认为它们使日期更具可读性。

如果您使用 DATE ，Oracle 接受这种格式的常量：

 select DATE '2018-01-25'

这非常方便。首先，支持合理的标准很好。其次，无论国际化设置如何，代码都是安全的。 Oracle 文档当然详细介绍了这一点； 这 是一个开始的地方。

原文由 Gordon Linoff 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

DATE 没有格式 - 它在内部存储为 7 个字节，代表年（2 个字节）和月、日、小时、分钟和秒（每个 1 个字节）。

'25-JAN-18' 不是日期 - 它是文本文字。

当你这样做时：

 INSERT INTO table_name ( date_column ) VALUES ( '25-JAN-18' );

Oracle 将尝试提供帮助，并使用用户会话的 NLS_DATE_FORMAT 参数作为格式模型执行从字符串到日期的隐式转换。因此，您的语句将被隐式转换为：

 INSERT INTO table_name ( date_column ) VALUES (
  TO_DATE(
    '25-JAN-18',
    ( SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT' )
  )
);

任何用户都可以在他们自己的会话中设置他们的 NLS 参数（所以你不 应该 依赖隐式转换，因为每个用户可以为他们自己的会话设置不同的设置，并且可以在会话中更改值）。相反，您应该：

• 使用日期文字：

   DATE '2018-01-25'

• 使用时间戳文字

  TIMESTAMP '2018-01-25 01:23:45'

• 使用 TO_DATE( date_string, format_string [, nls_values] ) 并明确使用格式模型：

   TO_DATE( '25-JUN-18', 'DD-MON-RR' )

如果您确实想在会话中更改 NLS_DATE_FORMAT ，那么您可以使用：

 ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

默认日期格式是什么？

由于 DATE 没有格式，所以这个问题没有意义。相反，如果我们问：

Oracle 用来在字符串和日期之间转换的默认会话参数 NLS_DATE_FORMAT 是什么？

这取决于 NLS_TERRITORY 会话参数（所以这取决于你在世界的哪个位置）：

 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 CAST( f.format AS VARCHAR2(12) ) 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;

输出日期格式和与该格式对应的地区列表：

 FORMAT       TERRITORIES
------------ ------------------------------------------------------------------
DD MON RRRR  THAILAND
DD-MM-RR     ALGERIA, BAHRAIN, INDIA, MOROCCO, THE NETHERLANDS, TUNISIA
DD-MM-RRRR   BANGLADESH, INDONESIA, ROMANIA, VIETNAM
DD-MON-RR    AMERICA, CHINA, HONG KONG, IRELAND, ITALY, PAKISTAN, TAIWAN,
             UNITED KINGDOM
DD-MON-RRRR  ISRAEL
DD.MM.RR     AUSTRIA, BELARUS, CIS, CROATIA, CZECH REPUBLIC, CZECHOSLOVAKIA,
             GERMANY, RUSSIA, SLOVAKIA, SLOVENIA, SWITZERLAND
DD.MM.RRRR   ALBANIA, AZERBAIJAN, ESTONIA, FINLAND, FYR MACEDONIA, ICELAND,
             KAZAKHSTAN, MACEDONIA, NORWAY, SERBIA AND MONTENEGRO, UKRAINE,
             YUGOSLAVIA
DD.MM.RRRR.  MONTENEGRO, SERBIA
DD.fmMM.RRRR ARMENIA
DD/MM/RR     AFGHANISTAN, BELGIUM, BRAZIL, CAMEROON, CATALONIA, CHILE, COLOMBIA,
             CONGO BRAZZAVILLE, CONGO KINSHASA, COSTA RICA, CYPRUS, DJIBOUTI,
             EGYPT, EL SALVADOR, FRANCE, GABON, GREECE, GUATEMALA, HONDURAS,
             IRAQ, IVORY COAST, JORDAN, KUWAIT, LEBANON, LIBYA, LUXEMBOURG,
             MAURITANIA, MEXICO, NEW ZEALAND, NICARAGUA, OMAN, PANAMA, PERU,
             PUERTO RICO, QATAR, SAUDI ARABIA, SINGAPORE, SOMALIA, SPAIN, SUDAN,
             SYRIA, UNITED ARAB EMIRATES, URUGUAY, VENEZUELA, YEMEN
DD/MM/RRRR   ARGENTINA, BAHAMAS, BERMUDA, ECUADOR, MALAYSIA, SENEGAL, TURKEY,
             UGANDA, ZAMBIA
DD/MON/RR    AUSTRALIA, SOUTH AFRICA, UZBEKISTAN
DD/fmMM/RRRR LAOS, NIGERIA
MM/DD/RRRR   PHILIPPINES
RR-MM-DD     CANADA, DENMARK, JAPAN
RR-MON-DD    HUNGARY
RR.MM.DD     PORTUGAL
RR/MM/DD     KOREA, POLAND
RRRR-MM-DD   BULGARIA, SWEDEN
RRRR-fmMM-DD CAMBODIA
RRRR.MM.DD   LATVIA, LITHUANIA
RRRR/fmMM/fm IRAN, SRI LANKA
fmDD-MM-RR   BOLIVIA
fmDD/MM/RR   PARAGUAY
fmDD/MM/RRRR BELIZE, ETHIOPIA, MALTA, NEPAL
fmDD/fmMM/RR MALDIVES
fmMM.DD.RRRR BOSNIA AND HERZEGOVINA
fmMM/DD/RRRR KENYA, TANZANIA

原文由 MT0 发布，翻译遵循 CC BY-SA 4.0 许可协议