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!

date manipultation issue reg

Discussion in 'SQL PL/SQL' started by laxman, Mar 1, 2010.

  1. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Dear all,
    i have one table

    Code (SQL):
    sql>DESC request
    Name                       NULL?             TYPE
    ---------------------- ---------       -----------------
    reqid                      NOT NULL   NUMBER(10)
    requestname                            Varchar2(20)
    STATUS                                       varchar2(10)
    lastmoddate                            DATE
     
    i need to retreive lastmoddate record based on some condition given below,also i have written query for each requirement,but date manipulation logic is correct or not i am not sure,kindly help me out to modify the logic if it is not correct.

    Requirement one: search requests that have been assigned but in pending state for more than 24hrs

    Code (SQL):
    sql> SELECT reqid,requestname FROM request WHERE lastmoddate<sysdate - 1 AND STATUS='pending';
    Requirement two: search requests that have been assigned but in escalated state for past 8 hrs
    Code (SQL):
    sql> SELECT reqid,requestname FROM request WHERE lastmoddate<sysdate - 8/24 AND STATUS='escalated';
     
    Requirement two: search requests that have been assigned but in closed state for two weeks
    Code (SQL):
    sql> SELECT reqid,requestname FROM request WHERE lastmoddate>sysdate - 14 AND STATUS='closed';
    Thanks n regards
    Laxman
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    All Fine Except last one. For the last one it should be < sign.

    Code (SQL):

    SELECT reqid,requestname FROM request WHERE lastmoddate<sysdate - 14 AND STATUS='closed';
     
    Or you can use <= bases on your requirement.
     
  3. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    thanks a lot sir.


    Regards
    Laxman