Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2008
    Posts
    26

    Question Unanswered: to display days and hours while subtracting two dates

    hi

    right now i m displaying only no of days
    select (TO_date(SYSDATE,'DD/MM/YYYY hh')-TO_DATE(SL1.REQUESTDATE,'DD/MM/YYYY hh')) AS PENDINGDAYS




    i wanna display no of days and hours in my query.

    if my hours are less than 24 then it should display hours for eg
    10 days and 23 hours like
    to display---10.23

    10 days and 24 hours like
    to display---11

    can u give me the query for this????

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    A little bit of mathematics could help; the result you currently have is "number of days" (and it is a decimal number). In order to get "hours", extract digits behind the decimal point and multiply it by 24 (24 as "number of hours in a day"). Of course, digits in front of decimal point will still represent number of days.

  3. #3
    Join Date
    Nov 2008
    Posts
    26
    can u give me example to calcutale no of days and hours ????????????

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Sure:
    Code:
    SQL> select trunc(sysdate) - to_date('17.11.2008 13', 'dd.mm.yyyy hh24') res
      2  from dual;
    
           RES
    ----------
    1.45833333
    
    SQL> -- 1.4583333  =>  1 = number of days (1 day)
    SQL> --            =>  0.4583333 = number of days (to be converted to hours)
    SQL> select 0.45833333 * 24 hours from dual;
    
         HOURS
    ----------
    10.9999999
    
    SQL> -- due to rounding issues, it is, actually, 11 hours
    SQL> -- so, the final result is 1 day and 11 hours
    SQL>
    I'll leave coding to you; feel free to present your code to the rest of the community (even if you don't succeed and need further help). However, I don't feel like doing your homework (at the moment, that is).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •