When we use nvl function for between clause with the date data type. If you may have this question when you execute it in the tools like Toad.
Another Tips for a date between with nvl function
Today I got a where clause
SELECT '1'
FROM dual
WHERE 1 = 1
AND SYSDATE BETWEEN NVL(:v_date_fm, SYSDATE) AND NVL(:v_date_to, SYSDATE);
Both parameters without given any value.But at last result without return any rows
No rows returned.
It makes me confused about that. So I take another script to verify the problem.
SELECT '1'
FROM DUAL
WHERE 1 = 1
AND 3 BETWEEN NVL(:v_number_fm, 3) AND NVL(:v_number_to, 3)
And it goes properly return 1 row with the value 1
so then I notice that there is one thing I missed. that is the data type with parameter value when it comes to date. Although it will matter a lot when it comes to char.When it goes properly when I change it to date.
This error logs for anyone.thanks for share it or give me some comment if it's helped.