Re: problem with date in oracle 10g

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Fri, 4 Jan 2008 06:25:34 -0800 (PST)
Message-ID: <ec0569fa-73f7-4c9c-8950-64754137fb4d@v29g2000hsf.googlegroups.com>

On Jan 4, 8:23 am, narayan..._at_gmail.com wrote:
> Hi All,
>     I have a doubt. I am writing a function which has to get the
> greatest date value among the columns in a row. say there are 5
> columns. I have to get the greatest date of those 5 columns and
> display the result.
>
> There might be chance that the date values might be null. In case if
> some two column values are null and other three have some date values
> i need to get the greatest of those three.
>
> In case if all are null the result will be null. Please some one help
> me in writing the function in Oracle to get the greatest date from 7
> parameters passed inside which are all of Type Date.
>
> Say the input is
>
> Date1       Date2               Date3       Date4        Date5
> Date6   Date7
> 12/12/2008  01/01/2007       null                null          3/3/2006
> 10/10/2008      null
> null          null               null        3/09/2004    null             null
> null
> 01/04/2006      null             null            null
> 4/04/2006        null   null
>
> The ouput should be like this
>
> Date
> 12/12/2008
> 3/09/2004
> 4/04/2006
>
> Please some one give me a way for this.

The GREATEST function can be used to accomplish the above by first converting the NULL values to an out of expected bound value (a very old date). A quick sample:
CREATE TABLE T1 (

  DATE1 DATE,
  DATE2 DATE,
  DATE3 DATE,
  DATE4 DATE,
  DATE5 DATE,
  DATE6 DATE,

  DATE7 DATE); INSERT INTO
  T1
VALUES(
  TO_DATE('12/12/2008','MM/DD/YYYY'),
  TO_DATE('01/01/2007','MM/DD/YYYY'),
  NULL,
  NULL,
  TO_DATE('03/03/2006','MM/DD/YYYY'),
  TO_DATE('10/10/2008','MM/DD/YYYY'),
  NULL); INSERT INTO
  T1
VALUES(

  NULL,
  NULL,
  NULL,

  TO_DATE('03/09/2004','MM/DD/YYYY'),
  NULL,
  NULL,
  NULL); INSERT INTO
  T1
VALUES(
  TO_DATE('01/04/2006','MM/DD/YYYY'),

  NULL,
  NULL,
  NULL,

  TO_DATE('04/04/2006','MM/DD/YYYY'),
  NULL,
  NULL); INSERT INTO
  T1
VALUES(

  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,

  NULL); SELECT
  GREATEST(

    NVL(DATE1,TO_DATE('01-JAN-1900','DD-MON-YYYY')),
    NVL(DATE2,TO_DATE('01-JAN-1900','DD-MON-YYYY')),
    NVL(DATE3,TO_DATE('01-JAN-1900','DD-MON-YYYY')),
    NVL(DATE4,TO_DATE('01-JAN-1900','DD-MON-YYYY')),
    NVL(DATE5,TO_DATE('01-JAN-1900','DD-MON-YYYY')),
    NVL(DATE6,TO_DATE('01-JAN-1900','DD-MON-YYYY')),
    NVL(DATE7,TO_DATE('01-JAN-1900','DD-MON-YYYY'))) MY_DATE

FROM
  T1;

MY_DATE

---

12-DEC-08
09-MAR-04
04-APR-06
01-JAN-00

The last row has a date of 1/1/1900, and we need to convert that back to a NULL value. The simple way would be to slide the above into an inline view and use a DECODE function:
SELECT
  DECODE(MY_DATE,TO_DATE('01-JAN-1900','DD-MON-YYYY'),NULL,MY_DATE) MY_DATE
FROM
  (SELECT
   GREATEST(

    NVL(DATE1,TO_DATE('01-JAN-1900','DD-MON-YYYY')),
    NVL(DATE2,TO_DATE('01-JAN-1900','DD-MON-YYYY')),
    NVL(DATE3,TO_DATE('01-JAN-1900','DD-MON-YYYY')),
    NVL(DATE4,TO_DATE('01-JAN-1900','DD-MON-YYYY')),
    NVL(DATE5,TO_DATE('01-JAN-1900','DD-MON-YYYY')),
    NVL(DATE6,TO_DATE('01-JAN-1900','DD-MON-YYYY')),
    NVL(DATE7,TO_DATE('01-JAN-1900','DD-MON-YYYY'))) MY_DATE

  FROM
    T1);

MY_DATE

---

12-DEC-08
09-MAR-04
04-APR-06

NULL Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Jan 04 2008 - 08:25:34 CST