甲骨文的默认日期格式是什么？

Question

我有一个Oracle DB，我不控制日期格式。我想知道日期格式是什么，以确保搜索喜欢

select * from search where search_date>='03/16/2016 00:00:00'

按预期工作。

Answer 1

不要这样做 - 你依赖于隐式数据类型转换，这会在某个时候失败。

您有两种选择：

1） 使用正确的 ANSI SQL 日期文本：

select * 
from search 
where search_date >= timestamp '2016-03-16 00:00:00';

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');

使用to_date()，您应该避免任何与语言相关的格式。使用月份的数字，而不是缩写（例如 'Mar'或'Apr'），因为它们再次依赖于客户端语言。

更多详细信息可以在手册中找到：
https://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements003.htm#SQLRF51062

---

永远不要依赖隐式数据类型转换。

Answer 2

您可以使用

查询获取所有NLS会话参数：

SELECT * FROM NLS_SESSION_PARAMETERS;

或者，如果您具有权限GRANT SELECT ON V_$PARAMETER TO YOUR_USERNAME;，则可以使用以下命令：

SHOW PARAMETER NLS;

如果您只想要日期格式，那么您可以执行以下任一操作：

SELECT * FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT';

或

SHOW PARAMETER NLS_DATE_FORMAT;

但是，您也可以使用与格式无关的 ANSI 日期（或时间戳）文本。ANSI 日期文本的格式为 DATE 'YYYY-MM-DD'，时间戳文本的格式为 TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF9' 。因此，您的查询将是：

select * from search where search_date>= DATE '2016-03-16'

或

select * from search where search_date>= TIMESTAMP '2016-03-16 00:00:00'

Answer 3

甲骨文的默认日期格式是什么？

日期没有任何格式。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_CHAR。

要进行比较，请使用具有正确格式模型的TO_DATE。

默认情况下，您看到的格式是特定于区域设置的 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'

您正在将 DATE 与字符串文本进行比较。始终使用TO_DATE和正确的格式掩码将字符串显式转换为日期。

TO_DATE('03/16/2016', 'MM/DD/YYYY')

或者，如果您只处理日期部分而不关心时间部分，请使用使用固定格式的 ANSI 日期文本DATE 'YYYY-MM-DD'

DATE '2016-03-16'

由于基于特定于区域设置的 NLS 设置的隐式数据类型转换，您可能很幸运能够获得输出。永远不要依赖隐式数据类型转换，它可能对你有用，对nls设置不同的其他人可能会失败。