In Our package we had where clause like below.
pp.start_date >= nvl(TO_DATE('01-JAN-2018', 'RRRR/MM/DD HH24:MI:SS'), pp.start_date)
It was working perfectly in customer environment. But since format was 'RRRR/MM/DD HH24:MI:SS' so we changed the date like below.
pp.start_date >= nvl(TO_DATE('2018/01/01', 'RRRR/MM/DD HH24:MI:SS'), pp.start_date)
Ideally it should work because we have given proper format. in the first case it is returning 679 rows but in second case it is returning 0 rows. 679 correct number of rows we have.
What is wrong with second NVL command?
The way I see it, both are wrong.
If date value is 2018/01/01, then its format is (I presume) yyyy/mm/dd (could be yyyy/dd/mm as well, can't tell just by looking at it). Where did hh24:mi:ss format come from? You never used it.
Therefore, I'd use date literal (which is always date 'yyyy-mm-dd')(as you aren't interested in time component anyway):
where pp.start_date >= date '2018-01-01'
No NVL function. Why? Because date literal can't be NULL.
You would use NVL if it were about some other column, e.g.
where pp.start_date >= nvl(a.some_date, date '2018-01-01')
which means: if a.some_date column is NULL use date '2018-01-01' instead.
[EDIT] If there is time component, after all, use TO_DATE function with appropriate format mask, e.g.
where pp.start_date >= to_date('2018-01-01 23:09', 'yyyy-mm-dd hh24:mi')