Dates and DECODE
How do I use the DECODE function with a date field? Please give me some examples. I am looking for exact date matching and date comparisons. I am using Oracle8i. If DECODE can not handle it, how do I go about it?
DECODE only does exact matching. For example, exact values representing "Today" and "Tomorrow" are easy to get, so you might use DECODE like this:
SELECT due_dt, DECODE ( TRUNC (due_dt), TRUNC (SYSDATE), 'Today', TRUNC (SYSDATE + 1), 'Tomorrow', 'Neither' ) AS due_text FROM tablex;
When you want to compare values (e.g., "Is date a before date b?," or "Is date a between dates b1 and b2?") Then the CASE statement is much more convenient.
SELECT due_dt, CASE WHEN TRUNC (due_dt) < TRUNC (SYSDATE) THEN 'Past' WHEN TRUNC (due_dt) BETWEEN TRUNC (SYSDATE) AND TRUNC (SYSDATE) + 6 -- Note: BETWEEN includes both end-points, so -- SYSDATE+0 through SYSDATE+6 is a full week THEN 'This Week' ELSE 'Later' END AS due_text FROM tablex;
CASE is a feature of ANSI SQL introduced into Oracle in version 8.1. See the earlier question on DECODE versus CASE for more examples.