使用to_date提供 ORA-01722 时出现问题:无效号码

Question

我正在尝试将两个字段放在一起以使用 case-when 创建一个 mm/dd/yyyy 日期字段，但它一直给我错误 ORA-01722：无效数字。

case when 
extract(month from t.date) >= t.month 
then to_date(t.month || '/' || '01' || '/' || extract(year from t.date), 'mm/dd/yyyy')
else to_date(t.month || '/' || '01' || '/' || extract(year from t.date)-1, 'mm/dd/yyyy')
end as effective_date

Answer 1

问题是 Oracle 在进行算术之前应用了串联运算符。所以你的 CASE 语句的 ELSE 分支：

to_date(t.month || '/' || '01' || '/' || extract(year from t.date)-1, 'mm/dd/yyyy')

将执行类似'02/01/2019' - 1.试图从字符串中减去一个(在这一点上至关重要的是，它是一个字符串而不是日期(不会不合理地导致 ORA-01722。

您需要做的是将提取年份包装在某种东西中，以在串联之前强制减号操作。这将做到这一点：

to_date(t.month || '/' || '01' || '/' || to_char(extract(year from t.date)-1), 'mm/dd/yyyy')

Answer 2

首先，@APC提供的解决方案对于OP提出的问题是必要且充分的。
但是，还有另一种选择，尤其是因为我对日期的字符串操作感到遗憾。将字符串转换为日期，然后使用日期函数(或根据需要使用时间戳(进行所有进一步处理。除了最终显示或导出之外，没有理由将日期转换为字符串。因此，我为以后的观众提供了以下替代方案。
提出的问题分为以下几点：
给定日期和目标月份 - 如果日期的月份与目标月份相同，则返回目标月份的第一个。 - 如果日期的月份是目标月份之前，则返回上一年目标月份
的第一个。

alter session set nls_date_format = 'yyyy-mm-dd';
with t as (select to_date('02/11/20','mm/dd/rr') src_dte, 1 tgt_mon from dual union all
select to_date('2020-02-11'),                  2         from dual union all
select to_date('11-Feb-2020','dd-mon-yyyy'),   3         from dual
)
select t.src_dte, tgt_mon
, case when extract(month from t.src_dte) >= t.tgt_mon 
then add_months(trunc(t.src_dte, 'year'), t.tgt_mon-1)
else add_months(trunc(t.src_dte, 'year'), t.tgt_mon-1-12)
end as effective_date
from t;

它是如何工作的。
所有日期函数都返回有效日期。
1. trunc(src_date(，'year'( 返回日期 01-1 月- 年
2.从上面到所需的月份只是添加必要的月数(0-11(。由于目标是tgt_mon减去 1，然后添加到 trunc 结果中得到1-tgt_mon 年。
3. 对于上一年，从上面取相同的结果并减去 1 年(12 个月(。没有函数减去月份，所以只需添加一个 -12。这给出了1-tgt_mon-1。

我相信这是一种比字符串操作更好的日期处理方法，但这只是我的意见。拿走你愿意的。