-
urgent
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
-
find the smallest difference
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
David Knight
OCP DBA 8i, 9i, 10g
-
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..
-
Here you get only one date which is near to the supplied date. You can remove MAX to get all dates...
Code:
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|