Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Location
    Bangalore
    Posts
    44

    Smile Unanswered: Time difference Between 2 dates

    Hi Oracle Gurus,

    I want a query or a function which gets the time difference between any 2 dates. I have a scenario is like this. I have a table having start_date and end_date as 2 date columns. i need to know the time difference between start_date and end_date, excluding times of saturdays and sundays.

    Thanks in advance
    Mahesh

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Here is a function that returns the difference in days, ignoring weekends:

    Code:
    CREATE OR REPLACE FUNCTION bdays_sql( p_d1 DATE, p_d2 DATE ) RETURN NUMBER
    IS
    BEGIN
      RETURN FLOOR( ABS(TRUNC(p_d2)-TRUNC(p_d1))/7)*5 
              + NVL(LENGTH(TRANSLATE( SUBSTR( '1234567123456'
                                            , TO_NUMBER(TO_CHAR(TRUNC( LEAST(p_d1,p_d2) ),'D'))
                                            , MOD(FLOOR(ABS(TRUNC(p_d1)-TRUNC(p_d2))),7)
                                            )
                                    , '671'
                                    , '6'
                                    )
                          )
                   ,0
                   );
    END;
    /
    For example:
    Code:
    SQL> select bdays_sql(SYSDATE-30,SYSDATE) from dual;
    
    BDAYS_SQL(SYSDATE-30,SYSDATE)
    -----------------------------
                               22
    You may want to tweak it depending on how you interpret the difference. For example (today is Thursday, tomorrow is Friday):
    Code:
    SQL> select bdays_sql(SYSDATE,sysdate+1) from dual;
    
    BDAYS_SQL(SYSDATE,SYSDATE+1)
    ----------------------------
                               1
    If you want that to be 2 days rather than one you will need to amend the code.

Posting Permissions

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