Oracle Apps Knowledge Sharing

Query to get hours between two dates excluding weekends

In this post, we have a custom function which we used to determine hours between two date excluding weekends. Hope this can act as a base to meet your requirements.

Custom Function: 

CREATE FUNCTION xxsh_timedif_without_wknds ( p_from_date IN DATE, 
                                             p_to_date   IN DATE 
                                           )
RETURN NUMBER
IS
ln_hours_between  NUMBER;
ld_start_date    DATE; 
ld_end_date      DATE; 
ln_num_of_wknds  NUMBER DEFAULT 0;
ln_return_value  NUMBER;
BEGIN 
  ln_hours_between := p_to_date - p_from_date; 
  
  IF TRUNC(p_to_date) = TRUNC(p_from_date)
     AND TO_CHAR(p_from_date,'DY','nls_date_language=english') NOT IN ('SAT','SUN')
     AND TO_CHAR(p_to_date,'DY','nls_date_language=english') NOT IN ('SAT','SUN')
  THEN
    ln_return_value := ln_hours_between;
    
  ELSIF ln_hours_between <= 2
     AND TO_CHAR(p_from_date,'DY','nls_date_language=english')  IN ('SAT','SUN')
     AND TO_CHAR(p_to_date,'DY','nls_date_language=english')  IN ('SAT','SUN')
  THEN 
    ln_return_value := 0;

  ELSE 
    IF TO_CHAR(p_from_date,'DY','nls_date_language=english')  = 'SAT'
    THEN
      ld_start_date := TRUNC(p_from_date+2);
    ELSIF TO_CHAR(p_from_date,'DY','nls_date_language=english')  = 'SUN'
    THEN
      ld_start_date := TRUNC(p_from_date+1);
    ELSE 
      ld_start_date :=  p_from_date;
    END IF;
    
    IF TO_CHAR(p_to_date,'DY','nls_date_language=english')  = 'SAT'
    THEN
      ld_end_date := TRUNC(p_to_date) - (1/(24*60*60));
    ELSIF TO_CHAR(p_to_date,'DY','nls_date_language=english')  = 'SUN'
    THEN
      ld_end_date := TRUNC(p_to_date-1) - (1/(24*60*60));
    ELSE 
      ld_end_date :=  p_to_date;
    END IF;
    
    SELECT COUNT(1)
      INTO ln_num_of_wknds
      FROM dual
     WHERE TO_CHAR(ld_start_date+ level-1,'DY','nls_date_language=english') IN ('SAT','SUN')  
   CONNECT BY LEVEL <= CEIL(ld_end_date - ld_start_date);
   
    ln_return_value := (ld_end_date - ld_start_date) - ln_num_of_wknds;
  END IF;  
   RETURN(ln_return_value * 24);
END xxsh_timedif_without_wknds;                                                     

Sample Call:

If you run the query on Friday, the below query will fetch 24 hours, whereas if you run the same query on Monday, then it will return 72 hours.

SELECT XXSH_TIMEDIF_WITHOUT_WKNDS(sysdate,sysdate+3) FROM dual;