title: How to exclude weekends when adding days in sql
body: <p>I have the following tables on oracle DB
  PRB_phases which contains:
  PRBID,CREATION_DATE,INSERTIONDATE,ENDDATE,PRIORITY,COMPLEXITY,PHASES
  and table RFQ_HOLD_TIME which contains 
  RFQID,HOLDDATE,UNHOLDDATE where i save the time that the problem is on hold status
  so on the following query except the information about the problem also calculate the result of holdtime and add it to the "phase"deadline but i want to exclude the weekend when add days for deadline phases an also when add time to deadline from the holdtime. </p>
  
  <p>the query is the following and it works the only thing that i want help is to exclude the weekends:</p>
  
  <pre><code>select  PRBID,CREATION_DATE,CURRENT_PHASES,NEXT_PHASES,CLASSIFICATION_DEADLINE,FEASIBILITY_DEADLINE,
  CASE          
              WHEN HUGE.CURRENT_PHASES = 'Classification'  and to_date(HUGE.CLASSIFICATION_DEADLINE,'DD/MM/RRRR hh12:mi') &lt;= trunc(SYSDATE)  THEN 'DELAYED'
              WHEN HUGE.CURRENT_PHASES = 'Feasibility'  and to_date(HUGE.FEASIBILITY_DEADLINE,'DD/MM/RRRR hh12:mi') &lt;= trunc(SYSDATE) THEN 'DELAYED'
              WHEN HUGE.CURRENT_PHASES = 'Classification'   and to_date(HUGE.CLASSIFICATION_DEADLINE,'DD/MM/RRRR hh12:mi') &gt;= trunc(SYSDATE) THEN 'OK'
              WHEN HUGE.CURRENT_PHASES = 'Feasibility'  and to_date(HUGE.FEASIBILITY_DEADLINE,'DD/MM/RRRR hh12:mi') &gt;= trunc(SYSDATE) THEN 'OK'                        
  END
  STATE
   from 
  (select
   b.PRBID,b.CREATIONDATE CREATION_DATE,b.PHASES CURRENT_PHASES,
   CASE
               WHEN phases = 'Classification' and INSERTIONDATE is not null THEN 'Feasibility'
               WHEN phases = 'Feasibility' and CreationDate is not null THEN 'Completed'
  END
  NEXT_PHASES,
  CASE
              WHEN b.phases = 'Classification' and b.INSERTIONDATE is not null and b.Priority = 1 and b.Complexity = 'minor' and a.holddate is not null and a.unholddate is not null THEN to_char(b.creationdate + 5 + floor(((a.unholddate-a.holddate)*24*60*60)/3600)/24,'dd/mm/rrrr hh12:mi')
              WHEN b.phases = 'Classification' and b.INSERTIONDATE is not null and b.Priority = 1 and b.Complexity = 'minor' and a.holddate is not null and a.unholddate is null THEN 'HOLD'
             ELSE to_char(creationdate+5,'dd/mm/rrrr hh12:mi')
  END
  CLASSIFICATION_DEADLINE,
  CASE
              WHEN b.phases = 'Feasibility' and b.INSERTIONDATE is not null and b.Priority = 1 and b.Complexity = 'minor' and a.holddate is not null and a.unholddate is not null THEN to_char(b.creationdate + 5 + floor(((a.unholddate-a.holddate)*24*60*60)/3600)/24,'dd/mm/rrrr hh12:mi')
              WHEN b.phases = 'Feasibility' and b.INSERTIONDATE is not null and b.Priority = 1 and b.Complexity = 'minor' and a.holddate is not null and a.unholddate is null THEN 'HOLD'
              ELSE to_char(creationdate+10,'dd/mm/rrrr hh12:mi')
  END
  FEASIBILITY_DEADLINE
  from PRB_PHASES b, PRB_HOLD_TIME a
  where a.PRBid = b.PRBid
  union
  select
  b.PRBID,b.CREATIONDATE CREATION_DATE,b.PHASES CURRENT_PHASES,
   CASE
               WHEN phases = 'Classification' and INSERTIONDATE is not null THEN 'Feasibility'
               WHEN phases = 'Feasibility' and CreationDate is not null THEN 'Completed'
  END
  NEXT_PHASES,
  CASE
              WHEN b.phases = 'Classification' and b.INSERTIONDATE is not null and b.Priority = 1 and b.Complexity = 'minor' and a.holddate is not null and a.unholddate is not null THEN to_char(b.creationdate + 5 + floor(((a.unholddate-a.holddate)*24*60*60)/3600)/24,'dd/mm/rrrr hh12:mi')
              WHEN b.phases = 'Classification' and b.INSERTIONDATE is not null and b.Priority = 1 and b.Complexity = 'minor' and a.holddate is not null and a.unholddate is null THEN 'HOLD'
             ELSE to_char(creationdate+5,'dd/mm/rrrr hh12:mi')
  END
  CLASSIFICATION_DEADLINE,
  CASE
              WHEN b.phases = 'Feasibility' and b.INSERTIONDATE is not null and b.Priority = 1 and b.Complexity = 'minor' and a.holddate is not null and a.unholddate is not null THEN to_char(b.creationdate + 5 + floor(((a.unholddate-a.holddate)*24*60*60)/3600)/24,'dd/mm/rrrr hh12:mi')
              WHEN b.phases = 'Feasibility' and b.INSERTIONDATE is not null and b.Priority = 1 and b.Complexity = 'minor' and a.holddate is not null and a.unholddate is null THEN 'HOLD'
                      ELSE to_char(creationdate+10,'dd/mm/rrrr hh12:mi')
  END
  FEASIBILITY_DEADLINE
  from PRB_PHASES b,PRB_HOLD_TIME a
  where a.PRBid &lt;&gt; b.PRBid)  HUGE
  </code></pre>