# Thread: Get Difference of hours

1. Registered User
Join Date
Jan 2005
Posts
4

## Unanswered: Get Difference of hours

I want to get difference of hours between 2 passed datetime.

Ex:

Date_1 = '23/01/2005 09.30'
Date_2 = '24/01/2005 10.30'

Difference hours = 25hrs

Kindly give me your valuable suggestions.

Thanks

2. Registered User
Join Date
Mar 2002
Location
Posts
1,137
select (date_2-date_1)*24 from dual;

Remember you can think of date fields as storing the number of days so multiplying by 24 gives you hours, 3600 gives you minutes and 86400 gives you seconds.

Alan

3. Registered User
Join Date
Jan 2005
Posts
4
Originally Posted by AlanP
select (date_2-date_1)*24 from dual;

Remember you can think of date fields as storing the number of days so multiplying by 24 gives you hours, 3600 gives you minutes and 86400 gives you seconds.

Alan

I tried all the possible methods but no use....I wrote my own function to solve this problem.......thanks.

Syed

4. Registered User
Join Date
Mar 2002
Location
Posts
1,137
Err.. you were using date fields werent you. If your using just character strings as oppossed to dates then use to_date to convert the character strings to dates and then use the query above, otherwise you might mind issues at month and year boundaries.

Alan

5. Registered User
Join Date
Aug 2004
Location
France
Posts
754
I wrote my own function to solve this problem
What did you want exactly ? Alan's suggestion should have greatly helped you.

If it's just a problem of formatting data and you wanted something like "25:20:39", you could have issued something like :
Code:
```select TO_CHAR(FLOOR((date_2-date_1)*24))||':'
||LTRIM(TO_CHAR(MOD((date_2-date_1)*1440, 60), '09'))||':'
||LTRIM(TO_CHAR(MOD((date_2-date_1)*86400, 60), '09'))
from dual;```
Could you please share your solution with us ?

Regards,

RBARAER

6. Registered User
Join Date
Jan 2005
Posts
4

## DateTime

Originally Posted by AlanP
Err.. you were using date fields werent you. If your using just character strings as oppossed to dates then use to_date to convert the character strings to dates and then use the query above, otherwise you might mind issues at month and year boundaries.

Alan
Dear Alan,

I got 2 dates, from which i want total hours. The above suggestion doesn't works.

date-1 -----> 25/01/2004 10:50
date-1 -----> 26/01/2004 12:50

Result is 26 hours. Just tell me a SQL for this. Thanks for ur valuable time.

Syed

7. Registered User
Join Date
Mar 2002
Location
Posts
1,137
Are these date type fields or are they character strings.

For character strings you can do

select 24*(to_date('26/01/2004 12:50','dd/mm/yyyy hh24:mi')- to_date('25/01/2004 10:50','dd/mm/yyyy hh24:mi')) from dual;

For dates remove the to_date bit.

Alan

8. Registered User
Join Date
Jan 2005
Posts
4

## thanks

Alan, thanks for this query...its gr8. Perfect one.

Syed

Originally Posted by AlanP
Are these date type fields or are they character strings.

For character strings you can do

select 24*(to_date('26/01/2004 12:50','dd/mm/yyyy hh24:mi')- to_date('25/01/2004 10:50','dd/mm/yyyy hh24:mi')) from dual;

For dates remove the to_date bit.

Alan

#### Posting Permissions

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