3 Replies Latest reply on Apr 9, 2020 8:16 AM by Prakash Jain

    CAI : Date Difference Function

    Lina Kanhye Active Member

      Hello, do we have a function in Cloud Application Integration to calculate the number of business days between two different datetime fields - excluding saturday and sunday.

       

      For example: Start Date: 2020-03-23 15:43:00 and End Date: 2020-04-07 17:17:00

       

      Thanks in advance

        • 1. Re: CAI : Date Difference Function
          Prakash Jain Guru

          HI Lina,

           

          There is no out of the box function to calculate this, however you can try the below Xquery logic for the same.

           

           

          let $start:= xs:date('2020-04-01')

          let $end:= xs:date('2020-04-08')

           

          let $dayOfWeekStart := xs:integer((xs:date($start) - xs:date('1901-01-06')) div xs:dayTimeDuration('P1D')) mod 7

             let $dayOfWeekEnd := xs:integer((xs:date($end) - xs:date('1901-01-06')) div xs:dayTimeDuration('P1D')) mod 7

           

          let $adjDayOfWeekStart := if($dayOfWeekStart = 0) then 7 else $dayOfWeekStart

             let $adjDayOfWeekEnd := if($dayOfWeekEnd = 0) then 7 else $dayOfWeekEnd

             return

              if($adjDayOfWeekStart <= $adjDayOfWeekEnd)

              then xs:integer((xs:integer(days-from-duration(xs:date($end) - xs:date($start)) div 7) * 5)

               + max(((min((($adjDayOfWeekEnd + 1), 6)) - $adjDayOfWeekStart), 0)))

              else xs:integer((xs:integer(days-from-duration(xs:date($end) - xs:date($start)) div 7) * 5)

               + min((($adjDayOfWeekEnd + 6) - min(($adjDayOfWeekStart, 6)), 5)))

           

          • 2. Re: CAI : Date Difference Function
            Lina Kanhye Active Member

            Hello Prakash,

             

            Thanks for the response.

            In fact, i have implemented something similar as detailed:

             

            Start Date: 2020-03-23 15:43:00

            End Date: 2020-04-07 17:17:00

             

             

            $Start_DT =util:format(fn:string($StartDate),"YYYY-MM-dd")

            $End_DT =util:format(fn:string($EndDate),"YYYY-MM-dd")

            $TotalNoDays =xs:date($End_DT)-xs:date($Start_DT)

            $NumDays =(xs:dayTimeDuration($TotalNoDays)) div xs:dayTimeDuration("P1D")

            --to get the number of weekend, i have performed div by 7

            $NumWeek = round($NumDays div 7)

            --to get the number of days for weekend

            $WeekdaysOnly = $NumDays - ($NumWeek*2)

            --exclude the weekend

            $NumofMonth =round((xs:dayTimeDuration($TotalNoDays)) div xs:dayTimeDuration("P30D"))

            • 3. Re: CAI : Date Difference Function
              Prakash Jain Guru

              Thanks Lina for sharing your Xquery code.  Could you mark it answered for the benefit of others.

               

               

              Regards,

              Prakash Jain