1

I am using the below code to pick the difference between two dates excluding weekends. However, the below method is not accurate. It gives issues when the difference between the two days are very less, e.g. 2 and it includes Saturday OR Sunday.

DAYS= Round((END_TIME-START_TIME)-2*FLOOR((END_TIME-START_TIME)/7)-DECODE(SIGN(TO_CHAR(END_TIME,'D')-
    TO_CHAR(START_TIME,'D')),-1,2,0)+DECODE(TO_CHAR(START_TIME,'D'),7,1,0)-
    DECODE(TO_CHAR(END_TIME,'D'),7,1,0),0)

Please help

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
SandyX
  • 21
  • 1
  • 6
  • What's about public holidays, e.g New Year or Independence Day? – Wernfried Domscheit Apr 05 '16 at 07:06
  • 3
    or http://stackoverflow.com/questions/14898357/calculate-business-days-in-oracle-sqlno-functions-or-procedure – Eduard Uta Apr 05 '16 at 07:10
  • hi what is the data type of the value that is returned by `TO_CHAR(START_TIME,'D')` I guess its a varchar, then how is the opreation `TO_CHAR(END_TIME,'D')-TO_CHAR(START_TIME,'D')` possible? – sql_dummy Apr 05 '16 at 09:44

0 Answers0