Firebird calc between date skip weekend

Question

I want to convert this sql code to firebird sql for find working days between two dates:

CREATE FUNCTION [dbo].fn_CountWeekDays
(
@fromdate Datetime,
@todate Datetime
)
RETURNS TABLE AS RETURN
(

 SELECT
(DATEDIFF(dd, @fromdate, @todate) + 1)
-(DATEDIFF(wk, @fromdate, @todate) * 2)
-(CASE WHEN DATENAME(dw, @fromdate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @todate) = 'Saturday' THEN 1 ELSE 0 END)
As NoOfWeekDays

)

thanks

Answer 1

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:

• DATEADD
• DATEDIFF
• EXTRACT