SQL Oracle 的默认日期格式是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36048519/
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
What is Oracle's Default Date Format?
提问by entpnerd
I have an Oracle DB, and I don't control the date format. I want to know what the date format is to ensure that searches like
我有一个 Oracle DB，我无法控制日期格式。我想知道日期格式是什么以确保搜索像
select * from search where search_date>='03/16/2016 00:00:00'
work as expected.
按预期工作。
回答by a_horse_with_no_name
Don't do that - you are relying on implicit data type conversion which is going to fail at some point.
不要那样做——你依赖于隐式数据类型转换，这在某些时候会失败。
You have two options:
您有两个选择：
1) Use a proper ANSI SQL date literal:
1) 使用适当的 ANSI SQL 日期文字：
select *
from search
where search_date >= timestamp '2016-03-16 00:00:00';
2) use to_date()(or to_timestamp()) and use a custom format.
2）使用to_date()（或to_timestamp()）并使用自定义格式。
select *
from search
where search_date >= to_date('03/16/2016 00:00:00', 'mm/dd/yyyy hh24:mi:ss');
With to_date()you should avoid any format that is language dependent. Use numbers for the month, not abbreviations (e.g. 'Mar'or 'Apr') because they again rely on the clientlanguage.
有了to_date()你应该避免任何格式依赖于语言。使用数字表示月份，而不是缩写（例如'Mar'或'Apr'），因为它们再次依赖于客户端语言。
More details can be found in the manual:
https://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements003.htm#SQLRF51062
更多细节可以在手册中找到：https:
//docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements003.htm#SQLRF51062
Neverrely on implicit data type conversion.
永远不要依赖隐式数据类型转换。
回答by MT0
You can get all the NLSsession parameters with the query:
您可以通过NLS查询获取所有会话参数：
SELECT * FROM NLS_SESSION_PARAMETERS;
or, if you have the permissions GRANT SELECT ON V_$PARAMETER TO YOUR_USERNAME;, you can use the command:
或者，如果您有权限GRANT SELECT ON V_$PARAMETER TO YOUR_USERNAME;，则可以使用以下命令：
SHOW PARAMETER NLS;
If you just want the date format then you can do either:
如果您只想要日期格式，那么您可以执行以下任一操作：
SELECT * FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT';
or
或者
SHOW PARAMETER NLS_DATE_FORMAT;
However, you could also use ANSI date (or timestamp) literals which are format agnostic. An ANSI date literal has the format DATE 'YYYY-MM-DD'and a timestamp literal has the format TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF9'. So your query would be:
但是，您也可以使用与格式无关的 ANSI 日期（或时间戳）文字。ANSI 日期文字具有格式DATE 'YYYY-MM-DD'，时间戳文字具有格式TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF9'。所以你的查询是：
select * from search where search_date>= DATE '2016-03-16'
or
或者
select * from search where search_date>= TIMESTAMP '2016-03-16 00:00:00'
回答by Lalit Kumar B
What is Oracle's Default Date Format?
Oracle 的默认日期格式是什么？
A DATEdoesn't have any format. Oracle does not store dates in the format you see. It stores it internally in 7 byteswith each byte storing different components of the datetimevalue.
一个日期没有任何格式。Oracle 不会以您看到的格式存储日期。它在内部以7 个字节存储它，每个字节存储日期时间值的不同组成部分。
Byte Description
---- -------------------------------------------------
1 Century value but before storing it add 100 to it
2 Year and 100 is added to it before storing
3 Month
4 Day of the month
5 Hours but add 1 before storing it
6 Minutes but add 1 before storing it
7 Seconds but add 1 before storing it
To display, use TO_CHARwith proper FORMAT MODEL.
要显示，请使用TO_CHAR和正确的FORMAT MODEL。
For comparing, use TO_DATEwith proper FORMAT MODEL.
为了进行比较，将TO_DATE与正确的FORMAT MODEL 一起使用。
What you see as a format by default, is your locale specific NLS settings.
默认情况下，您看到的格式是特定于语言环境的 NLS 设置。
SQL> select parameter, value from v$nls_parameters where parameter='NLS_DATE_FORMAT';
PARAMETER VALUE
--------------- ----------------------------------------------------------------
NLS_DATE_FORMAT DD-MON-RR
SQL> select sysdate from dual;
SYSDATE
---------
17-MAR-16
SQL> select to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'MM
-------------------
03/17/2016 12:48:41
SQL>
search_date>='03/16/2016 00:00:00'
搜索日期>='03/16/2016 00:00:00'
You are comparing a DATEwith a stringliteral. Always, explicitly convert the string into date using TO_DATEand proper format mask.
您正在将DATE与字符串文字进行比较。始终使用TO_DATE和正确的格式掩码将字符串显式转换为日期。
TO_DATE('03/16/2016', 'MM/DD/YYYY')
Or, if you dealing only with the date part and not concerned with the time portion, then use the ANSI date literalwhich uses a fixed format DATE 'YYYY-MM-DD'
或者，如果您只处理日期部分而不关心时间部分，则使用使用固定格式的ANSI 日期文字DATE 'YYYY-MM-DD'
DATE '2016-03-16'
You might just be lucky to get an output due to an implicit datatype conversionbased on your locale specific NLS settings. Never ever rely on implicit datatype conversion, it might work for you, might fail for others where the nls settings are different.
由于基于您的语言环境特定 NLS 设置的隐式数据类型转换，您可能只是幸运地获得了输出。永远不要依赖隐式数据类型转换，它可能对您有用，但对于 nls 设置不同的其他人可能会失败。
