Problem solve Get help with specific problems with your technologies, process and projects.

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',
               )  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,
            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.

Dig Deeper on Using Oracle PL-SQL