代码之家 › 专栏 › 技术社区 › Dmitry Kompot

ORA-01839 where子句中to_date的“指定月份的日期无效”

to-date date-format oracle sql

Dmitry Kompot · 技术社区 · 9 年前

我有以下疑问( BOCRTNTIME - varchar e.g 2015-02-28 12:21:45, VIEW_BASE_MARIX_T - some view ):

select BOCRTNTIME
    from VIEW_BASE_MARIX_T
    where to_date(substr(BOCRTNTIME,1,10),'YYYY-MM-DD')
        between (to_date ('2016-01-01', 'YYYY-MM-DD'))
            and (to_date ('2016-02-01', 'YYYY-MM-DD'))

执行时我收到错误:

ORA-01839:  "date not valid for month specified"

我想可能有不正确的数据 BOCRTNTIME ,因此执行以下查询:

select distinct
         substr(BOCRTNTIME,1,8),
         substr(BOCRTNTIME,9,2)
  from VIEW_BASE_MARIX_T
 order by substr(BOCRTNTIME,9,2);

但一切看起来都很好: http://pastebin.com/fNjP4UAu . 同样,以下查询执行时没有任何错误:

select to_date(substr(BOCRTNTIME,1,10),'YYYY-MM-DD') 
  from VIEW_BASE_MARIX_T;

我已经试过添加 trunc() 致所有人 to_date() 但没有运气。此外,我还创建了pl/sql过程,该过程采用逐个项目的形式 VIEW_BASE_MARIX_T 并将其转换为日期-一切正常。你知道为什么我在第一次查询时出错吗?

UPD:对视图中使用的表的查询工作正常,但在视图中没有

更新2:我们几乎没有相同产品的环境,但只在一个环境中出错

UPD3:通过在视图中使用的表中搜索无效日期解决了问题

4 回复 | 直到 9 年前

MT0 9 年前

注释太长了-创建一个简单的函数来测试日期:

CREATE FUNCTION is_Valid_Date(
  in_string VARCHAR2,
  in_format VARCHAR2 DEFAULT 'YYYY-MM-DD'
) RETURN NUMBER DETERMINISTIC
AS
  dt DATE;
BEGIN
  dt := TO_DATE( in_string, in_format );
  RETURN 1;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 0;
END;
/

然后您可以执行以下操作:

SELECT BOCRTNTIME
FROM   VIEW_BASE_MARIX_T
WHERE  is_Valid_Date( substr(BOCRTNTIME,1,10) ) = 0;

您可能会发现,4月、6月、9月或11月有一个当月31日的条目,或者2月的值大于28/29(尽管我在粘贴的数据中看不到类似的内容)。

否则,您可以尝试使用ANSI日期文本:

SELECT BOCRTNTIME
FROM   VIEW_BASE_MARIX_T
WHERE  to_date(substr(BOCRTNTIME,1,10),'YYYY-MM-DD') between DATE '2016-01-01' and DATE '2016-02-01';

或者,更简单的是,给定输入的格式:

SELECT BOCRTNTIME
FROM   VIEW_BASE_MARIX_T
WHERE  substr(BOCRTNTIME,1,10) between '2016-01-01' and '2016-02-01';

Ricardo Arnold 9 年前

我认为可能发生的是,Oracle正在将谓词推送到视图的底层表中。

您是否尝试运行查询

select to_date(substr(BOCRTNTIME,1,10),'YYYY-MM-DD') BOCRTNTIME
from MY_TABLE

而不是查询视图?

您也可以使用NO_PUSH_PRED提示来确认这一点

select /*+ NO_PUSH_PRED(VIEW_BASE_MARIX_T) */
BOCRTNTIME
from VIEW_BASE_MARIX_T
where 
to_date(substr(BOCRTNTIME,1,10),'YYYY-MM-DD') between (to_date ('2016-01-01', 'YYYY-MM-DD')) and (to_date ('2016-02-01', 'YYYY-MM-DD'))

StephaneM 9 年前

错误消息显示您的月份中有一个无效的日期。用以下方法检查您的数据:

-- Check for months with 30 days
select substr(BOCRTNTIME,9,2), substr(BOCRTNTIME,6,2) from VIEW_BASE_MARIX_T where to_number(substr(BOCRTNTIME,6,2)) in (4,6,9,11) and to_number(substr(BOCRTNTIME,9,2))>30;

-- Check for february
select substr(BOCRTNTIME,9,2), substr(BOCRTNTIME,6,2) from VIEW_BASE_MARIX_T where to_number(substr(BOCRTNTIME,6,2))=2 and to_number(substr(BOCRTNTIME,9,2))>28;

Emil Moise 9 年前

这可能是一个很长的机会,但 between 它的语法中没有括号,你试过删除它们吗?

select
    BOCRTNTIME
    from VIEW_BASE_MARIX_T
    where 
    to_date(substr(BOCRTNTIME,1,10),'YYYY-MM-DD') between to_date ('2016-01-01', 'YYYY-MM-DD') and to_date ('2016-02-01', 'YYYY-MM-DD')