see how this works:
case when to_char(sysdate,'dd')>8 then
end as newdate
You're comparing a string to a number.
There is nothing that says that Oracle might decide to compare them like this: '12' > '8' returns false (string comparison)
To solve, pick one of these methods:
- force a number-to-number comparison by explicitly converting the string to a number ( ie to_number( to_char( ... ) ) )
- compare a date to a date: sysdate > trunc(sysdate,'MM') + 8
I prefer to compare a date to a date.