Your current SQL Server function is a table-valued function (it returns a table), the closest equivalent in Firebird 3 (and earlier) would be a selectable stored procedure:
create or alter procedure CountWeekDays(fromdate timestamp, todate timestamp)
returns (NoOfWeekDays bigint)
as
declare normalizedFrom timestamp;
declare normalizedTo timestamp;
begin
normalizedFrom = dateadd(-1 * extract(weekday from fromdate) day to fromdate);
normalizedTo = dateadd(-1 * extract(weekday from todate) day to todate);
NoOfWeekDays = (DATEDIFF(day, fromdate, todate) + 1)
-(DATEDIFF(week, normalizedFrom , normalizedTo) * 2)
-(CASE WHEN extract(weekday from fromdate) = 0 THEN 1 ELSE 0 END)
-(CASE WHEN extract(weekday from todate) = 6 THEN 1 ELSE 0 END);
-- Suspend is necessary to make it selectable!
suspend;
end
The normalization of the to and from dates to Sunday for the week difference is necessary, as unfortunately for datediff(week ...)
Firebird doesn't count the weeks, but periods of 7 days between two dates, so for example datediff(week, date'2017-07-14', date'2017-07-20')
(a Friday to the next Thursday) is 0, not 1. Normalizing to the Sunday of the week will ensure that the week difference is calculated correctly.
One word of warning: I have only tested this with a (small) selection of dates and compared it to the output of the SQL Server function, and I have not tested with a time component, you might want to verify it more thoroughly.
Given the nature of the data, you could also have used a scalar function in SQL Server. The equivalent of a scalar function would be a PSQL function (which was introduced in Firebird 3)
create or alter function fn_CountWeekDays(fromdate timestamp, todate timestamp)
returns bigint
as
declare normalizedFrom timestamp;
declare normalizedTo timestamp;
begin
normalizedFrom = dateadd(-1 * extract(weekday from fromdate) day to fromdate);
normalizedTo = dateadd(-1 * extract(weekday from todate) day to todate);
return (DATEDIFF(day, fromdate, todate) + 1)
-(DATEDIFF(week, normalizedFrom , normalizedTo) * 2)
-(CASE WHEN extract(weekday from fromdate) = 0 THEN 1 ELSE 0 END)
-(CASE WHEN extract(weekday from todate) = 6 THEN 1 ELSE 0 END);
end
References: