This post is, largely, a note to myself. You may find it useful if you've developed the same habits I have, or it may be old news to you.
Recently, I've been working on changing from using dates to timestamps (with local time zone). Generally, this is pretty easy, but I've run into an issue which required me to change how I do things.
My biggest problem is that I use this pattern a lot:
select ...
decode(sign(trunc(date_col) - trunc(sysdate)),
1, 'Future', 0, 'Today', 'Past') when_is_this,
...
from ...
For dates, this works fine, because the difference between two dates is the number of days between them. But the difference between two timestamps is an interval, and you can't take the sign of an interval, so you get an error: “ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND”. Very annoying. The solution, of course, is to use CASE instead of DECODE, because you can use relational operators in CASE:
select ...
case
when trunc(date_col) > trunc(sysdate)
then 'Future'
when trunc(date_col) = trunc(sysdate)
then 'Today'
else 'Past'
end when_is_this,
...
from ...
Honestly, the CASE syntax is easier to read than DECODE, since it's trivial to tell what the conditions are and what the return values are; with DECODE, you need to count commas and/or rely on good formatting habits. And it's more flexible, since you can have your entire condition in the WHEN statement, so you're not restricted to comparing one value to a list of possibilities. I'm not sure why I use DECODE far more often than CASE; I suspect that it's because CASE was introduced after I learned SQL, but I haven't been able to verify that yet. Regardless, I'm going to be using it more in the future.