日期类型的并发程序参数给出错误ORA-01843:不是有效的月份

Question

Select ordered_date, order_number, customer_id
From order_tbl xx       
Where  NVL(:P_ORDER_NUMBER, xx.order_number) = xx.order_number
 and xx.ordered_date between NVL(trunc(TO_DATE(:P_FROM_DATE, 'YYYY/MM/DD HH24:MI:SS')), xx.ORDERED_DATE) and NVL(trunc(TO_DATE(:P_TO_DATE, 'YYYY/MM/DD HH24:MI:SS'), xx.ORDERED_DATE)
    AND NVL(:P_CUSTOMER, xx.customer_id) = xx.customer_id>

java.sql.SqlDataException:ORA-01843:不是有效月份

我可以为这个参数使用什么值集？
和我可以传递什么格式？

Answer 1

如果您的参数像您所说的那样已经是日期，那么就不应该为它们调用to_date()。这将使用会话的NLS_DATE_FORMAT将日期隐式转换为字符串，然后尝试使用指定的格式模型将该字符串转换回日期。

如果NLS_DATE_FORMAT碰巧与您指定的内容匹配，那么这将起作用，但您不应该依赖于这种情况，也不应该进行隐式转换；你做的工作比你需要的要多。

所以只需将其简化为：

Select ordered_date, order_number, customer_id
From order_tbl xx       
Where  NVL(:P_ORDER_NUMBER, xx.order_number) = xx.order_number
and xx.ordered_date between NVL(trunc(:P_FROM_DATE), xx.ORDERED_DATE)
   and NVL(trunc(:P_TO_DATE), xx.ORDERED_DATE)
AND NVL(:P_CUSTOMER, xx.customer_id) = xx.customer_id>