使用 BETWEEN 子句比较日期和时间戳

Question

我的查询的基本结构是这样的：

SELECT....FROM....
WHERE SYSTIMESTAMP BETWEEN DateA and DateB.

我正在尝试各种方法，其中之一是，但出现错误，例如ORA-01861：文本与格式字符串不匹配，或者在某些情况下"不是有效的月份">

WHERE
SYSTIMESTAMP BETWEEN 
      TO_TIMESTAMP(TO_DATE(DateA,'YYYY-MM-DD HH24:MI:SS'),  'DD/MM/YYYY HH24:MI:SSFF') AND 
      TO_TIMESTAMP(TO_DATE(DateB,'YYYY-MM-DD HH24:MI:SS'),  'DD/MM/YYYY HH24:MI:SSFF') 

DateA 和 DateB 列的类型为 DATE，而 SYSTIMESTAMP 显然是 TIMESTAMP。

我的 SQL 开发人员上的 NLS 设置是

• 对于日期：'MM/DD/YYYY HH24:MI:SS'
• 对于时间戳：'DD-MON-RR HH.MI.SSXFF AM'

请帮助我确定问题。

Answer 1

TO_TIMESTAMP 和 TO_DATE 都采用字符串并分别使用指定的格式掩码将其转换为时间戳/日期。

因此，执行TO_TIMESTAMP(TO_DATE(..)...)没有意义 - 尤其是当您的原始列已经采用日期格式时！你需要一个或另一个。

通过执行TO_TIMESTAMP(TO_DATE(<date_column>..)...)，您正在做的是强制oracle执行一些从日期到字符串的隐式转换，然后再回到日期，然后再转换回字符串，以便可以将其转换回时间戳。它看起来像这样：

TO_TIMESTAMP(TO_CHAR(TO_DATE(TO_CHAR(<date_column>, <nls_date_format>), <your_date_format>), <nls_date_format>), <your_timestamp_format>).

这太复杂了，对吧？另外，如果其中一种格式不匹配，那么您将遇到遇到的错误。

如果要将日期转换为时间戳，可以使用CAST，例如：

where systimestamp between cast(datea as timestamp) and cast(dateb as timestamp)

或者，您可以让 Oracle 处理从日期到时间戳的隐式转换，然后只执行以下操作：

where systimestamp between datea and dateb

或者通过使用 sysdate 而不是 systimestamp 来完全避免这个问题，即：

where sysdate between datea and dateb

Answer 2

您不需要转换来检查timestamp与date;例如：

SQL> desc testDate
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DATEA                                              DATE
 DATEB                                              DATE
SQL> select * from testDate;
DATEA     DATEB
--------- ---------
21-MAY-17 23-MAY-17
19-MAY-17 21-MAY-17
SQL> select *
  2  from testDate
  3  where systimestamp between dateA and dateB ;
DATEA     DATEB
--------- ---------
21-MAY-17 23-MAY-17
SQL>

Answer 3

如果您已经拥有 DATE 格式的时间戳，则无需将其转换为时间戳。只需使用：

WHERE SYSTIMESTAMP BETWEEN DateA AND DateB