Oracle SQL Developer 获取早于 n 个月的表行

Question

在 Oracle SQL Developer 中，我有一个名为 t1 的表，其中有两列 col1 定义为 NUMBER(19,0)，col2 定义为 TIMESTAMP(3)。 我有这些行

col1           col2
1     03/01/22 12:00:00,000000000
2     03/01/22 13:00:00,000000000
3     26/11/21 10:27:11,750000000
4     26/11/21 10:27:59,606000000
5     16/12/21 11:47:04,105000000
6     16/12/21 12:29:27,101000000

我的 sysdate 看起来像这样：

select sysdate from dual;

    SYSDATE
    03/03/22

我想创建一个存储过程（SP），它将删除超过 2 个月的行并显示消息 n 行已删除

但是当我执行此语句时，

select * from t1 where to_date(TRUNC(col2), 'DD/MM/YY') < add_months(sysdate, -2);

我没有得到前 2 行我的 t1 表。我得到超过 2 行

1     03/01/22 12:00:00,000000000
2     03/01/22 13:00:00,000000000

我怎样才能得到这些行并删除它？

Answer 1

在 Oracle 中，DATE 数据类型是由 7 个字节（世纪、世纪年份、月、日、小时、分钟和秒）组成的二进制数据类型。它始终具有所有这些组件，并且从不以特定格式（例如DD/MM/RR）存储。

您的客户端应用程序（即 SQL Developer）可以选择将二进制 DATE 值格式化为 DD/MM/RRDISPLAY > 但这是您正在使用的客户端应用程序的功能，而不是数据库的功能。

当您显示整个值时：

SELECT TO_CHAR(ADD_MONTHS(sysdate, -2), 'YYYY-MM-DD HH24:MI:SS') AS dt FROM DUAL;

然后它输出（取决于时区）：

<表类=“s-表”>
<标题>

DT

<正文>

2022-01-03 10:11:28

如果您将其与您的值进行比较，您会发现 2022-01-03 12:00:00 不是“超过 2 个月前”，因此它不会被匹配。

您似乎想要的不是“超过 2 个月前”，而是“等于或超过 2 个月，忽略时间部分，前”；您可以使用：

SELECT *
FROM   t1
WHERE  col2 < add_months(TRUNC(sysdate), -2) + INTERVAL '1' DAY;

或

SELECT *
FROM   t1
WHERE  TRUNC(col2) <= add_months(TRUNC(sysdate), -2);

（注意：第一个查询将使用 col2 上的索引，但第二个查询不会；它需要 TRUNC( col2) 而不是。）

---

另外，不要在已经是 DATE 或 TIMESTAMP 的列上使用 TO_DATE > 数据类型。 TO_DATE 采用字符串作为第一个参数，而不是 DATE 或 TIMESTAMP，因此 Oracle 将使用 TO_CHAR 执行隐式转换> 如果格式模型不匹配，那么您将引入错误（并且由于任何用户都可以随时在其会话参数中设置自己的日期格式，因此您可能会遇到一个用户的错误，而其他用户则不会出现这些错误，并且非常严重）很难调试）。
db<>fiddle 此处

Answer 2

也许只是：

select * 
from t1 
where col2 < add_months(sysdate, -2);