将NVL与日期转换函数一起使用会返回ORA-01843:不是有效的月份错误

Question

当我没有将日期传递到:P_FROM_DATE和:P_TO_DATE参数中时，下面的SQL(Oracle(会出现ORA-01843: not a valid month错误。由于某些原因，它不接受备用日期值(sysdate-30、sysdate-1(。

如果我将日期传递到这两个参数中，它确实可以正常工作。我如何修改它才能工作？

AND pha.CREATION_DATE BETWEEN     
NVL(to_date(to_char((:P_FROM_DATE),'YYYY-MM-DD')||' 00:00:00','YYYY-MM-DD HH24:Mi:SS') , sysdate-30)
AND 
NVL( to_date(to_char((:P_TO_DATE),'YYYY-MM-DD')||' 23:59:59','YYYY-MM-DD HH24:Mi:SS')  , sysdate-1)

Answer 1

这是因为您在错误的地方应用了NVL函数。您应该将其应用于参数本身，而不是整个表达式：

AND pha.CREATION_DATE BETWEEN 
to_date(nvl(:P_FROM_DATE, to_char(sysdate - 30, 'yyyy-mm-dd')) ||' 00:00:00', 'YYYY-MM-DD HH24:Mi:SS')
AND 
to_date(nvl(:P_TO_DATE  , to_char(sysdate - 1 , 'yyyy-mm-dd')) ||' 23:59:59', 'YYYY-MM-DD HH24:Mi:SS');

例如(其中我使用SQL*Plus和替换变量；您仍然会在代码中使用绑定变量(：

SQL> with pha (name, creation_date) as
2    (select 'Little', date '2022-11-01' from dual union all
3     select 'Foot'  , date '2019-08-25' from dual
4    )
5  select *
6  from pha
7  where 1 = 1
8  AND pha.CREATION_DATE BETWEEN
9    to_date(nvl('&P_FROM_DATE', to_char(sysdate - 30, 'yyyy-mm-dd')) ||' 00:00:00', 'YYYY-MM-DD HH24:Mi:SS')
10  AND
11    to_date(nvl('&P_TO_DATE'  , to_char(sysdate - 1 , 'yyyy-mm-dd')) ||' 23:59:59', 'YYYY-MM-DD HH24:Mi:SS');
Enter value for p_from_date:                 --> NULL values passed to
Enter value for p_to_date:                   --> both parameters
NAME   CREATION_D
------ ----------
Little 2022-11-01
SQL>

或者，将值传递给查询：

SQL> /
Enter value for p_from_date: 2019-07-13
Enter value for p_to_date: 2019-09-30
NAME   CREATION_D
------ ----------
Foot   2019-08-25
SQL>