1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Query Help

Discussion in 'SQL PL/SQL' started by kiran.marla, Jan 14, 2011.

  1. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hi all,

    I want the days difference...
    please refer the code..

    Code (SQL):
    1* SELECT trunc(sysdate+12 - sysdate+6) days FROM DUAL
    SQL> /

          DAYS
    ----------
            18

    SQL> SELECT sysdate+12 FROM dual;

    SYSDATE+1
    ---------
    26-JAN-11

    SQL> SELECT sysdate+6 FROM dual;

    SYSDATE+6
    ---------
    20-JAN-11

    SQL> SELECT to_date('26-JAN-11') - to_date('20-JAN-11') days FROM dual;

          DAYS
    ----------
             6
    my ans to be 6. when i use sysdate i am unable to get correct answer. If i hardcode the value, then getting. How to get correct answer with using sysdate...
     
  2. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    Kiran, you are missing brackets in the statement. Your expression simple adds 12 and 6 instead of subtracting them. Check the below one

    Code (SQL):
    SQL> ed
    Wrote file afiedt.buf

      1* SELECT trunc((sysdate+12) - (sysdate+6)) days FROM DUAL
    SQL> /

          DAYS
    ----------
             6
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    But the better practice is to use TRUNC Over Date field (and not on Number --> here day difference)

    Code (SQL):

    SQL> SELECT SYSDATE FROM DUAL;

    SYSDATE
    --------------------
    18-JAN-2011 11:08:08

    SQL> SELECT SYSDATE - TO_DATE('12-JAN-2011 18:00:00','DD-MON-YYYY HH24:MI:SS') D
    IFF FROM dual;

          DIFF
    ----------
     5.7144213

    SQL> SELECT TRUNC(SYSDATE - TO_DATE('12-JAN-2011 18:00:00','DD-MON-YYYY HH24:MI:
    SS'
    )) FROM DUAL;

    TRUNC(SYSDATE-TO_DATE('12-JAN-201118:00:00','DD-MON-YYYYHH24:MI:SS'))
    ---------------------------------------------------------------------
                                                                        5

    SQL> SELECT TRUNC(SYSDATE) - TRUNC(TO_DATE('12-JAN-2011 18:00:00','DD-MON-YYYY H
    H24:MI:SS'
    )) FROM DUAL;

    TRUNC(SYSDATE)-TRUNC(TO_DATE('12-JAN-201118:00:00','DD-MON-YYYYHH24:MI:SS'))
    ----------------------------------------------------------------------------
                                                                               6

    SQL>