sql - ORA-01847 - SELECT 没有返回结果

Question

我在 OFO 模式中有一个表 TIGER。

我有一个数据类型为 DATE 的列 AS_OF_DATE。

我在此列中的值如下:

2017-01-31 00:00:00
2017-02-28 00:00:00
2017-03-31 00:00:00

但我也有 1 个不需要的值(我想删除，但我想先通过 sql 语句SELECT它):

 0030-09-20 17:00:00

我使用 TOAD 在模式浏览器中查看这些值。

我还可以使用 select 返回值，这个有效，它返回结果:

SELECT AS_OF_DATE from OFO.TIGER where AS_OF_DATE='2017-01-31'

但是下面的选择不起作用:

SELECT AS_OF_DATE from OFO.TIGER where AS_OF_DATE='0030-09-20'

它给我一个错误:

ORA-01847: day of month must be between 1 and last day of month tips

日期的格式显然有误，但不知何故有人设法添加了该值，现在我想删除它(所有具有 AS_OF_DATE='0030-09-20' 的行)。

Answer 1

您的查询依赖于邪恶的隐式数据类型转换。 '2017-01-31' 是一个字符串常量不是 DATE 常量。此转换的规则由客户端 的 NLS 设置定义。

在 Oracle 中有两种方法可以指定正确的 DATE 常量:

使用 ANSI SQL 日期文字

SELECT * 
FROM ofo.tiger 
WHERE as_of_date = DATE '2017-01-31';

或

SELECT * 
FROM ofo.tiger 
WHERE as_of_date = DATE '0030-09-20';

ANSI DATE 文字始终 以 ISO 格式 yyyy-mm-dd 指定日期，因此没有关于 1930 与 2030 的隐式转换规则

使用 Oracle 的 to_date() 函数:

SELECT * 
FROM ofo.tiger 
WHERE as_of_date = to_date('0030-09-20', 'YYYY-MM-DD');

通过指定 format mask年份也是明确的，不会发生隐式转换。