Results 1 to 8 of 8
  1. #1
    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. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    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. #3
    Join Date
    Jan 2005
    Posts
    4
    Quote 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. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    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. #5
    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. #6
    Join Date
    Jan 2005
    Posts
    4

    DateTime

    Quote 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. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    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. #8
    Join Date
    Jan 2005
    Posts
    4

    thanks

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

    Syed


    Quote 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
  •