Firebird calc在日期之间跳过周末

您当前的SQL Server函数是一个表值函数(它返回一个表),Firebird 3(及更早版本)中最接近的等效函数是一个可选择的存储过程:

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

到 和 从…起 datediff(week ...) Firebird不计算周数,但计算两个日期之间7天的周期,例如 datediff(week, date'2017-07-14', date'2017-07-20') (星期五到下一个星期四)为0,而不是1。将标准化为一周的星期日将确保正确计算周差。

考虑到数据的性质,您也可以在SQL Server中使用标量函数。标量函数的等价物是 PSQL function (在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

• DATEADD
• DATEDIFF
• EXTRACT