How to get business days or hours between two dates

Question

How can I calculate business hours or days between two dates in oracle 10g?

For example we have two dates; 14/08/2012 9:30 and 16/08/2012 12:00 And we have working hours 09:30 to 18:30 in weekdays.

How can I calculate working hours or days excluding National Holidays, Sat & Sun with oracle 10g?

Answer 1

Amazing!! as follow this answer , I create my own with lunch time for 8 hours of work , 8- 12-13-17h30 , and it work fine. I comment out output line to understand how this function work.

create or replace FUNCTION business_hours_holiday(
              in_start_dt IN DATE DEFAULT SYSDATE ,
              in_end_dt   IN DATE DEFAULT SYSDATE )
              RETURN NUMBER DETERMINISTIC
    IS
    d                 NUMBER; -- Hours of either start_dt or end_dt after midnight
    return_val        NUMBER; -- Total number of working hours
    start_dt          DATE := LEAST(in_start_dt, in_end_dt); -- In case dates were in wrong order
    end_dt            DATE := GREATEST(in_start_dt, in_end_dt); -- In case dates were in wrong order
    is_holiday_start  NUMBER;
    is_holiday_end    NUMBER;
BEGIN
    SELECT
        COUNT(1)
    INTO is_holiday_start
    FROM
        user.noneworking_day
    WHERE
            to_date(noneworkingdate, 'yyyy-mm-dd') >= DATE '2023-01-01'
        AND to_date(noneworkingdate, 'yyyy-mm-dd') = trunc(start_dt);

    dbms_output.put_line('The value Is_holiday_start is ' || is_holiday_start);
    SELECT
        COUNT(1)
    INTO is_holiday_end
    FROM
        user.noneworking_day
    WHERE
            to_date(noneworkingdate, 'yyyy-mm-dd') >= DATE '2023-01-01'
        AND to_date(noneworkingdate, 'yyyy-mm-dd') = trunc(end_dt);

    dbms_output.put_line('The value Is_holiday_end is ' || is_holiday_end);

    WITH all_days AS (
        SELECT
            trunc(start_dt) + level - 1 AS a_dt
        FROM
            dual
        CONNECT BY
            level <= 1 + trunc(end_dt) - trunc(start_dt)
        MINUS
        SELECT
            to_date(noneworkingdate, 'yyyy-mm-dd') AS noneworkingdate
        FROM
            user.noneworking_day
        WHERE
            to_date(noneworkingdate, 'yyyy-mm-dd') >= DATE '2023-01-01'
    )
    SELECT
        SUM(8)
    INTO return_val
    FROM
        all_days
    WHERE
        to_char(a_dt, 'Dy', 'NLS_DATE_LANGUAGE = ''ENGLISH''') NOT IN ( 'Sat', 'Sun' );

    dbms_output.put_line('The value return_val_0 is ' || return_val);
--           Adjust hours from start_dt, if necessary
              IF
        to_char(start_dt, 'Dy', 'NLS_DATE_LANGUAGE = ''ENGLISH''') NOT IN ( 'Sat', 'Sun' ) 
          ----
                AND nvl(is_holiday_start, 0) = 0 /*KHONG TON TAI TRONG TEP NGAY NGHI*/

              THEN
        d := 24 * ( start_dt - trunc(start_dt) ); -- get interval by hours 
                    dbms_output.put_line('The value d is ' || d);
        IF d >= 17.5 THEN -- Don't count start_dt itself
             return_val := return_val - 8; -- dur = 0 if start after working end 18h30 
             dbms_output.put_line('The value return_val case 1.1 is ' ||return_val);
        ELSIF d BETWEEN 12 AND 13 THEN
            return_val := return_val - ( d - 8.5 ) + ( d - 12 );

            dbms_output.put_line('The value return_val start lunch is ' || return_val);
        ELSIF d >= 13 THEN -- Don't count part of start_dt -- buoi chieu 
                          return_val := return_val - ( d - 8.5 ) + 1;  -- if start in working time then = total time - 8.5 (working start) 
                          dbms_output.put_line('The value return_val case 1.2 is ' ||return_val);
        ELSIF d >= 8.5 THEN -- Don't count part of start_dt -- buoi chieu 
                          return_val := return_val - ( d - 8.5 );
        ELSE
            dbms_output.put_line('No start date condition are made');
        END IF;

    END IF;

    dbms_output.put_line('The value return_val if_1 is ' || return_val);
    dbms_output.put_line('--- end if 1');
--           Adjust hours from end_dt, if necessary

              IF
        to_char(end_dt, 'Dy', 'NLS_DATE_LANGUAGE = ''ENGLISH''') NOT IN ( 'Sat', 'Sun' )
        AND nvl(is_holiday_end, 0) = 0 /*KHONG TON TAI TRONG TEP NGAY NGHI*/

              THEN
        d := 24 * ( end_dt - trunc(end_dt) );
        dbms_output.put_line('The value d2 is ' || d);
        IF d <= 8.5 THEN -- Don't count end_dt itself 
                          return_val := return_val - 8;  -- if time end before working start then dur in that day (for end) = 0 
                          dbms_output.put_line('The value return_val case 2.1 is ' ||return_val);
        ELSIF d BETWEEN 12 AND 13 THEN
            return_val := return_val - ( 17.5 - 13 );
            dbms_output.put_line('The value return_val end lunch is ' || return_val);
        ELSIF d <= 12 THEN -- Don't count part of end_dt
                          return_val := return_val - ( 17.5 - d ) + 1;
        ELSIF d <= 17.5 THEN -- Don't count part of end_dt
                          return_val := return_val - ( 17.5 - d ); -- if time end after working start then dur in that day then dur = 9 - (17.5 - 15) 
                          dbms_output.put_line('The value return_val case 2.2 is ' ||return_val);
        ELSE
            dbms_output.put_line('No end date condition are made');
        END IF;

        dbms_output.put_line('The value return_val_2 is ' || return_val);
    END IF;

    dbms_output.put_line('The value return_val_final is ' || return_val);

          IF in_start_dt > in_end_dt THEN
            return_val  := -return_val;
          END IF;
        RETURN return_val;
          END;

Answer 2

I just did something like that. Here is an sql code piece to calculate the work done between stime and etime by employees in a table T

create table t ( NAME varchar(50), stime date, etime date, clockin number,clockout number );
insert into t values ( 'JOHN', to_date( '18/12/2003 11:40','dd/mm/yyyy hh24:mi'), to_date( '22/12/2003 14:00', 'dd/mm/yyyy hh24:mi'),8, 17 );
insert into t values ( 'JOHN', to_date( '19/12/2003 13:40','dd/mm/yyyy hh24:mi'), to_date( '21/12/2003 15:00', 'dd/mm/yyyy hh24:mi'),8, 17 );
insert into t values ( 'TOM', to_date( '19/12/2003 13:40','dd/mm/yyyy hh24:mi'), to_date( '21/12/2003 15:00', 'dd/mm/yyyy hh24:mi'),8, 17 );


with oo as (SELECT LEVEL-1 rn FROM dual CONNECT BY LEVEL <= 365) --JUST A TABLE WITH INTEGER RECORDS FROM 1 To 365 
select
t.NAME ,sum(least( trunc(stime)+18.5/24+rn, etime )-greatest( stime, trunc(stime)+9.5/24+rn))*24 as WorkHours -- Get workhours between 09:30-18:30 
from oo
inner join t on oo.rn < (trunc(etime)-trunc(stime)+1) 
      and to_char(stime+rn,'Dy') not in ( 'Sat', 'Sun' )  --For eliminating Weekends
      and to_char(trunc(stime)+rn,'DD.MM')  != '04.07'   -- For eliminating Holidays
group by t.NAME 

You can delete the group by line and remove the sum function to see how it works by generating rows for each day worked.

Answer 3

Found the solution for calculating Business Hours excluding weekends and holidays list in table.

https://forums.oracle.com/forums/thread.jspa?messageID=9322860

        create or replace
        FUNCTION business_hours(
              in_start_dt IN DATE DEFAULT SYSDATE ,
              in_end_dt   IN DATE DEFAULT SYSDATE )
            RETURN NUMBER DETERMINISTIC
          IS
            --  business_hours returns the number of work hours (9.30 am through 6.30 pm,
            --  Monday through Friday) between in_start_dt and in_end_dt.
            --  If in_start_dt > in_end_dt, the results will be <= 0.
            d          NUMBER;                                    -- Hours of either start_dt or end_dt after midnight
            end_dt     DATE := GREATEST (in_start_dt, in_end_dt); -- In case dates were in wrong order
            return_val NUMBER;                                    -- Total number of working hours
            start_dt   DATE := LEAST (in_start_dt, in_end_dt);    -- In case dates were in wrong order
          BEGIN
          WITH all_days AS
            (SELECT TRUNC(start_dt) + LEVEL - 1 AS a_dt
            FROM dual
              CONNECT BY LEVEL <= 1 + TRUNC (end_dt) - TRUNC (start_dt)
            MINUS
            SELECT hol_dt FROM holiday
            )
          SELECT SUM (9)
          INTO return_val
          FROM all_days
          WHERE TO_CHAR ( a_dt , 'Dy' , 'NLS_DATE_LANGUAGE = ''ENGLISH''' ) NOT IN ('Sat', 'Sun');
          -- Adjust hours from start_dt, if necessary
          IF TO_CHAR ( start_dt , 'Dy' , 'NLS_DATE_LANGUAGE = ''ENGLISH''' ) NOT IN ('Sat', 'Sun') THEN
            d := 24 * (start_dt - TRUNC (start_dt));
            IF d >= 18.5 THEN -- Don't count start_dt itself
              return_val := return_val - 9;
            ELSIF d > 9.5 THEN -- Don't count part of start_dt
              return_val := return_val - (d - 9.5);
            END IF;
          END IF;
          -- Adjust hours from end_dt, if necessary
          IF TO_CHAR ( end_dt , 'Dy' , 'NLS_DATE_LANGUAGE = ''ENGLISH''' ) NOT IN ('Sat', 'Sun') THEN
            d := 24 * (end_dt - TRUNC (end_dt));
            IF d <= 9.5 THEN -- Don't count end_dt itself
              return_val := return_val - 9;
            ELSIF d < 18.5 THEN -- Don't count part of end_dt
              return_val := return_val - (18.5 - d);
            END IF;
          END IF;
          IF in_start_dt > in_end_dt THEN
            return_val  := -return_val;
          END IF;
          RETURN return_val;
        END business_hours ;

Answer 4

You can't. It's as simple as that. National holidays vary the world over, they vary year by year and extra ones can be added or taken away at any time. Additionally some jurisdictions carry over national holidays that fall on the weekend and have them the next week; others don't.

You'll need to create a calender table and flag national holidays / weekends etc in this.

For instance

create table calender
  ( day date
  , weekend varchar2(1)
  , holiday varchar2(1)
    );

Then insert some data into it...

 insert into calender (day, weekend)
 select trunc(sysdate + level)
      , case when to_date(sysdate + level,'fmDAY') in ('SATURDAY','SUNDAY') 
                  then 'Y' else 'N' end
   from dual
connect by level <= 365

Lastly, manually update what you count as a national holiday in there.

You can then select working days, depending on how you populated it with something like this:

select count(*)
  from calender
 where day between :startdate and :enddate
   and weekend = 'N'
   and holiday = 'N'