Is there a built-in function to calculate the number of workdays between two dates?

[reimer_pods]

After checking the docs, I don't thinks so, but I might have overlooked it. To meet the requirement, I was able write a function for that purpose, based on someone else's code for Oracle.

But I'd prefer a built-in solution, something like

DateDiff (workday, @startdate, @enddate).

[MarkCulp · Accepted solution]

There is no builtin procedure (function) to compute the number of workdays between two dates... but as you have already alluded to it is easy to write such a function. Example:

create or replace function number_of_workdays( in @date1 date, in @date2 date )
returns int
begin
  if @date1 > @date2 then
    -- swap dates so that date1 is before date2
    begin
      declare @tdate date;
      set @tdate = @date1;
      set @date1 = @date2;
      set @date2 = @tdate;
    end;
  end if;
  return datediff( day, @date1, @date2 ) + 1   -- total number of days in interval (inclusive)
         - 2*datediff( week, @date1, @date2 )  -- minus 2*number of sundays (inc d2 but not d1)
         - ( if datepart( weekday, @date2 ) = 7 then 1 else 0 endif ) -- less 1 if end on Sat.
         - ( if datepart( weekday, @date1 ) = 1 then 1 else 0 endif ) -- less 1 if start on Sun.
         ;
end;

If you are careful and don't need the date-order check in the above function then it becomes a 'one liner' which will be inlined by the SQL Anywhere execution engine.

[reimer_pods · Accepted solution]

Thanks, Mark, that looks very much like the procedure I've created.