时间戳

首页 > 解决方案 > ORA-01841：（完整）年份必须介于 -4713 和 +9999 之间，并且不能为 0 提示

sql - ORA-01841：（完整）年份必须介于 -4713 和 +9999 之间，并且不能为 0 提示

问题描述

我想对大于01.01.2020DATUM的我的表列的所有值进行分组。当我运行查询时：test_tbl

SELECT to_date("DATUM", 'YYYYMM')
FROM test_tbl
WHERE to_date("DATUM", 'YYYYMM') >= to_date('2020-01-01' ,'YYYY-MM-DD')
GROUP BY to_date("DATUM", 'YYYYMM')

我收到以下错误

ORA-01841：（完整）年份必须介于 -4713 和 +9999 之间，并且不能为 0。

该表test_tbl如下所示：

数据 (varchar2)
201701
202001
201901
201801
202003

当我只运行没有 WHERE CLAUSE 的 GROUP BY 时，日期转换有效并且没有 NULL 值或类似的东西

SELECT to_date("DATUM", 'YYYYMM')
FROM test_tbl
GROUP BY to_date("DATUM", 'YYYYMM')

`

标签： sqloracle

解决方案

这是一个数据错误。在 DATUM 列的某处，您有一个无法转换为日期的字符串。当我们将数据存储为错误的数据类型时，这始终是一种风险。

如果您使用的是 Oracle 12c R2 或更高版本，您可以使用如下查询轻松定位错误行：

select * from your_table
where validate_conversion(datum as date, 'yyyymm') = 0

如果您使用的是早期版本的数据库，您可以创建一个执行类似操作的函数......

create or replace function is_date(p_str  in varchar2
                                  ,p_mask in varchar2 := 'yyyymm' ) return number is
  n pls_integer;
begin
  declare
    dt date;
  begin
    dt := to_date(p_str, p_mask);
    n := 1;
  exception
    when others then
      n := 0;
  end;
  return n;
end;
/

像validate_conversion()这样返回 1 表示有效日期，0 表示无效日期。

select * from your_table
where is_date(datum, 'yyyymm') = 0

这种方法更安全，因为它应用了 Oracle 的实际日期验证机制。然而，使用模式匹配正则表达式等让我们对传递无法转换为日期的字符串的弱模式开放。

推荐阅读