SQL Oracle中的日期减法-尝试通过减去"yyyy/mm/dd“和"yyyy/mm”来查找months_between

Question

作为更大查询的一部分，需要在SQL oracle表中查找两个日期之间的月份差异。

问题是，date#1的格式是"yyyy/mm/dd"，而date#2的格式是"yyyy/mm“。获取以下错误:日期格式图片在转换整个输入字符串之前结束。

下面是我的问题：

select count(distinct(aqs.quote_ref_id)), substr(aqs.quote_date,1,7), MONTHS_BETWEEN
       (to_date(qp.policy_effective_date, 'yyyy/mm/dd'), to_date(qp.property_exp_date, 'yyyy/mm')) as Num_Months
from live.auto_quote_summary aqs join live.quote_profile qp on qp.quote_ref_id = aqs.quote_ref_id

等。

你知道我可能做错了什么吗？

Answer 1

错误消息非常清楚。在其中一列中，您必须具有比您正在使用的格式模型更多的信息。policy_effective_date的年-月-日大于年-月-日(也许hh24:mi:ss?)或者property_exp_date有超过一年的月份。(不一定在所有的行中，但至少在一行中。)

要缩小范围，您可以尝试更简单的查询。如果你使用select to_date(policy_effective_date, 'yyyy/mm/dd') from live.quote_profile会发生什么？然后对other date列尝试相同的方法。找出哪些地方的数据可能不是您认为的格式。

演示(使用SQL*Plus)：

SQL> select to_date('2017/05', 'yyyy/mm') as dt from dual;

DT
----------
2017-05-01

1 row selected.


SQL> select to_date('2017/05/23', 'yyyy/mm') as dt from dual;
select to_date('2017/05/23', 'yyyy/mm') as dt from dual
               *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

Answer 2

您可以只转换所需的前"n“个字符：

select count(distinct(aqs.quote_ref_id)), substr(aqs.quote_date, 1, 7),
       months_between(to_date(substr(qp.policy_effective_date, 1, 10), 'yyyy/mm/dd'),
                      to_date(substr(qp.property_exp_date, 1, 7), 'yyyy/mm')
                     ) as Num_Months
from live.auto_quote_summary aqs join
     live.quote_profile qp
     on qp.quote_ref_id = aqs.quote_ref_id