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

# CAI : Date Difference Function

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

• ###### 1. Re: CAI : Date Difference Function

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

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

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

• ###### 2. Re: CAI : Date Difference Function

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

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

Regards,

Prakash Jain