# Thread: to display days and hours while subtracting two dates

1. Registered User
Join Date
Nov 2008
Posts
26

## 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. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,113
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. Registered User
Join Date
Nov 2008
Posts
26
can u give me example to calcutale no of days and hours ????????????

4. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,113
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
•