Oracle中的日期SQL引发错误- ORA-01858错误消息- ORA-01858:在需要数字的地方找到了非数字字符01858

gkl3eglg  于 2023-05-28  发布在  Oracle
关注(0)|答案(1)|浏览(299)
SELECT TO_DATE(pa.actual_exp_date, 'YYYYMMDD') - TO_DATE('20230531', 'YYYYMMDD')
         AS out_days
FROM   pre_agrees pa
       JOIN pass p ON pa.pre_id = p.pass_id
WHERE  pa.prepaid_status = 'A'
AND    to_char(pa.actual_exp_date, 'YYYYMMDD') >= '20230531'
AND    actual_exp_date IS NOT NULL
AND    to_char(p.effective_date, 'YYYYMMDD') <= '20230531';
yyyllmsg

yyyllmsg1#

永远不要在已经是DATE数据类型的值上使用TO_DATE
如果pre_agrees.actual_exp_datepass.effective_date都是DATE列,则:

SELECT TRUNC(pa.actual_exp_date) - DATE '2023-05-31' AS out_days
FROM   pre_agrees pa
       JOIN pass p ON pa.pre_id = p.pass_id
WHERE  pa.prepaid_status  =  'A'
AND    pa.actual_exp_date >= DATE '2023-05-31'
AND    p.effective_date   <  DATE '2023-05-31' + 1;
  • 注意:actual_exp_date IS NOT NULL过滤器是不必要的,因为pa.actual_exp_date >= DATE '2023-05-31'只有在列为NOT NULL时才为true。*

如果pre_agrees.actual_exp_datepass.effective_date都是VARCHAR2列,那么(除了将日期存储为字符串是不好的做法之外):

SELECT TO_DATE(pa.actual_exp_date, 'YYYYMMDD') - DATE '2023-05-31' AS out_days
FROM   pre_agrees pa
       JOIN pass p ON pa.pre_id = p.pass_id
WHERE  pa.prepaid_status = 'A'
AND    pa.actual_exp_date >= '20230531'
AND    p.effective_date <= '20230531';

相关问题