urgent

[thakur_at]

Hi all

Can I display Nearest dates before 31-dec-2002 from the table?
my data table shows this..
P_REF P_ID P_DT P_RSN
---------- ---------- --------- -------
D052 1916 24-JUL-00 new
d052 2779 04-JAN-01 act
d052 5156 01-OCT-02 act
d053 1920 24-JUL-00 new
d053 3134 09-MAY-01 act
d053 4587 11-OCT-02 act

here nearest dates to 31-dec-02 are 01-oct-02 and 11-oct-02
How to get these dates with the help of sql query?

awaiting reply...

thanks in advance
Ami

[dknight]

Find the difference between the given date and 31-DEC-2002.

Then find the entry with the smallest difference.

This might help:

SQL> select to_date('31-DEC-2002', 'DD-MON-YY') - SYSDATE
2 from dual;

TO_DATE('31-DEC-2002','DD-MON-YY')-SYSDATE
------------------------------------------
18.4627546

[thakur_at]

Thanks
but the problem is ..
Query can be able to search for any year..
not just restricted to 31-dec-2002 it can be for year 1996 or for 2001
awaiting reply..

[Sameer]

Here you get only one date which is near to the supplied date. You can remove MAX to get all dates...

SQL> SELECT * FROM testdate ORDER BY datum;

        ID DATUM
---------- ----------
         1 21.11.1996
         1 29.07.1999
         1 27.11.1999
         1 25.06.2000
         1 21.01.2001
         1 21.04.2001
         1 30.07.2001
         1 04.05.2002
         1 15.05.2002
         1 22.07.2002
         1 23.08.2002
         1 03.10.2002
         1 07.10.2002
         1 30.10.2002
         1 12.11.2002
         1 21.11.2002
         1 22.11.2002
         1 30.11.2002
         1 13.12.2002
         1 14.12.2002
         1 15.12.2002

21 rows selected.

SQL> SELECT MAX(datum) FROM testdate WHERE datum < (SELECT MAX(datum) FROM testdate
  2   WHERE datum = TO_DATE('&date','DD.MM.YYYY'));
Enter value for date: 22.07.2002
old   2:  where datum = to_date('&date','DD.MM.YYYY'))
new   2:  where datum = to_date('22.07.2002','DD.MM.YYYY'))

MAX(DATUM)
----------
15.05.2002

SQL> /
Enter value for date: 27.11.1999
old   2:  where datum = to_date('&date','DD.MM.YYYY'))
new   2:  where datum = to_date('27.11.1999','DD.MM.YYYY'))

MAX(DATUM)
----------
29.07.1999

SQL> /
Enter value for date: 23.08.2002
old   2:  where datum = to_date('&date','DD.MM.YYYY'))
new   2:  where datum = to_date('23.08.2002','DD.MM.YYYY'))

MAX(DATUM)
----------
22.07.2002

SQL>

Sameer