SQL Oracle 的默认 DATE 格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/50163432/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle's default DATE format
提问by BigRedEO
First time using Oracle SQL (I'm used to MySQL). I'm finding conflicting info on what the default date format is. After several attempts having to use TO_DATE with my INSERT INTO my_table statements, I finally found the database I'm using expects DD-MON-YY (i.e. 25-JAN-18). Yet on various pages here in stackoverflow and elsewhere, I see some that say default is YYYYMMDD or DD/MM/YYYY or YYYY-MM-DD. Why so many conflicting pieces of information?
第一次使用 Oracle SQL（我已经习惯了 MySQL）。我发现有关默认日期格式的信息存在冲突。在多次尝试将 TO_DATE 与我的 INSERT INTO my_table 语句一起使用后，我终于找到了我使用的数据库期望 DD-MON-YY（即 25-JAN-18）。然而，在 stackoverflow 和其他地方的各个页面上，我看到一些说默认是 YYYYMMDD 或 DD/MM/YYYY 或 YYYY-MM-DD。为什么会有这么多相互矛盾的信息？
采纳答案by Gordon Linoff
Oracle, as well as other databases, allows you to set the default format. Out of the box, the format is (typically) DD-MON-RR, where "RR" refers to a two-digit year. This is a pretty lousy format, from the perspective of ambiguity (two digit year?) and internationalization (for what countries is that actually the default?). But Oracle has been around a long, long time.
Oracle 以及其他数据库都允许您设置默认格式。开箱即用的格式是（通常）DD-MON-RR，其中“RR”是指两位数的年份。从歧义（两位数年份？）和国际化（哪些国家/地区实际上是默认设置？）的角度来看，这是一种非常糟糕的格式。但是 Oracle 已经存在了很长时间。
Standard formats are also defined by ISO, the International Standards Organization. They settled on something more like YYYY-MM-DD. Actually, the hyphens are optional, but I think they make the date much more readable.
标准格式也由国际标准组织 ISO 定义。他们选择了更像 YYYY-MM-DD 的东西。实际上，连字符是可选的，但我认为它们使日期更具可读性。
Oracle accepts constants in this format, if you use DATE:
Oracle 接受这种格式的常量，如果你使用DATE：
select DATE '2018-01-25'
This is very handy. First, it is nice to support reasonable standards. Second, the code is safe, regardless of internationalization settings. Oracle documentation of course covers this in detail; hereis one place to start.
这非常方便。首先，支持合理的标准很好。其次，无论国际化设置如何，代码都是安全的。Oracle 文档当然详细介绍了这一点；这是一个开始的地方。
回答by MT0
A DATEhas no format - it is stored internally as 7-bytesrepresenting year (2 bytes) and month, day, hour, minute and second (1 byte each).
ADATE没有格式——它在内部存储为7 个字节，代表年（2 个字节）和月、日、时、分和秒（每个 1 个字节）。
'25-JAN-18'is not a date - it is a text literal.
'25-JAN-18'不是日期 - 它是文本文字。
When you do:
当你这样做时：
INSERT INTO table_name ( date_column ) VALUES ( '25-JAN-18' );
Oracle will try to be helpful and perform an implicit cast from a string to a date using the NLS_DATE_FORMATparameter for the user's session as the format model. So, your statement will be implicitly converted to:
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' )
)
);
Any user can set their NLS parameters in their own session (so you should neverrely on implicit conversion as each user can have a different settings for their own session and can change the values mid-session). Instead you should:
任何用户都可以设置自己的NLS参数在自己的会话（所以你应该永远依靠隐式转换为每个用户都可以拥有自己的会话不同的设置，并且可以更改值中期会议）。相反，您应该：
Use a Date literal:
DATE '2018-01-25'Use a Timestamp literal
TIMESTAMP '2018-01-25 01:23:45'Use
TO_DATE( date_string, format_string [, nls_values] )and explicitly use a format model:TO_DATE( '25-JUN-18', 'DD-MON-RR' )
使用日期文字：
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' )
If you do want to change the NLS_DATE_FORMATin your session then you can use:
如果您确实想更改NLS_DATE_FORMAT会话中的 ，则可以使用：
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
What the default date format is?
默认日期格式是什么？
Since a DATEdoes not have a format, this question does not make sense. Instead if we ask:
由于 aDATE没有格式，所以这个问题没有意义。相反，如果我们问：
What is the default
NLS_DATE_FORMATsession parameter that Oracle uses to convert between strings and dates?
NLS_DATE_FORMATOracle 用于在字符串和日期之间进行转换的默认会话参数是什么？
It depends on the NLS_TERRITORYsession parameter (so it depends where you are in the world):
这取决于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;
Outputs the date format and the list of territories corresponding to that format:
输出日期格式和与该格式对应的领土列表：
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
回答by thatjeffsmith
DATEs are DATEs - the format determines how the DATE will be displayed when you query your data.
DATE 是 DATE - 格式决定了查询数据时 DATE 的显示方式。
If you don't supply a format and use the TO_CHAR function, we will provide the DATE back in the default NLS_DATE_FORMAT - which is defined in the database, but can also be specified for your session.
如果您不提供格式并使用 TO_CHAR 函数，我们将在默认 NLS_DATE_FORMAT 中提供 DATE - 它在数据库中定义，但也可以为您的会话指定。
For your session -
对于您的会议 -
select * from NLS_SESSION_PARAMETERS
where PARAMETER = 'NLS_DATE_FORMAT';
Mine is 'DD-MON-YYYY', so when I query SYSDATE:
我的是 'DD-MON-YYYY'，所以当我查询 SYSDATE 时：
SQL> select sysdate from dual;
SYSDATE
-----------
03-MAY-2018
When working with DATEs, it's generally best practice NOT to assume a specific DATE format. So when both INSERT or SELECT data with DATEs, be EXPLICIT. For example.
使用 DATE 时，通常最好不要假设特定的 DATE 格式。因此，当 INSERT 或 SELECT 数据都带有 DATE 时，要明确。例如。
SQL> drop table JUST_DATES;
Table JUST_DATES dropped.
SQL>
SQL> create table JUST_DATES (
2 DATE1 date
3 );
Table JUST_DATES created.
SQL>
SQL> insert into JUST_DATES values ( to_date('01-01-2018','MM-DD-YYYY') );
1 row inserted.
SQL>
SQL> select to_char(
2 DATE1,
3 'MON/DD/RR'
4 )
5 from JUST_DATES;
TO_CHAR(DATE1,'MON
------------------
JAN/01/18
Now, to answer your question - what's the default? Well, it depends.
现在，回答您的问题 - 默认值是什么？这要看情况。
It is derived from your NLS_TERRITORY (docs)
它源自您的 NLS_TERRITORY（文档）
But, if you remember best practices, and you never assume what the default is, and you are explicit with your date formats when working with DATEs, you should be ok.
但是，如果您记得最佳实践，并且从不假设默认值是什么，并且在使用 DATE 时明确指定日期格式，那么您应该没问题。
The best guide we have for this topic is our Globalization Support Guide. This is the section of interest to you.
我们针对该主题的最佳指南是我们的全球化支持指南。这是您感兴趣的部分。
回答by Nikhil Misal
Only thing you have to take care while inserting date using TO_DATEis how you parse it.
Example:
What will not workto_date('31-03-2016','dd-mon-yyyy')
使用插入日期时唯一需要注意的TO_DATE是如何解析它。
示例：
什么行不通to_date('31-03-2016','dd-mon-yyyy')
What will workto_date('31-03-2016','dd-mm-yyyy')
to_date('31-Mar-2016','dd-Mon-yyyy')
什么会起作用to_date('31-03-2016','dd-mm-yyyy')
to_date('31-Mar-2016','dd-Mon-yyyy')
The date you are passing to to_dateand format should match.31 - ddMar - Mon2016 - yyyy
您传递给的日期to_date和格式应该匹配。31 - ddMar - Mon2016 - yyyy
