DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: urgent

  1. #1
    Join Date
    Feb 2002
    Posts
    15

    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

  2. #2
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185

    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

  3. #3
    Join Date
    Feb 2002
    Posts
    15
    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..

  4. #4
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    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
  •  


Click Here to Expand Forum to Full Width