Add business days in date in Postgresql function

I have to convert a SQL Server procedure to PostgreSQL that adds number of days to given date. I did some research and found out a solution but it only works for positive number of days, when I pass it a negative number (to move back from given date) it never works. Following is the function:

CREATE OR REPLACE FUNCTION public.add_business_day(
    from_date date,
    num_days integer)
    RETURNS date
    LANGUAGE 'sql'

    COST 100
    VOLATILE 
    
AS $BODY$
    select d
    from (
        select d::date, row_number() over (order by d)
        from generate_series(from_date+ 1, from_date+ num_days * 2 + 5, '1d') d
        where 
            extract('dow' from d) not in (0, 6) 
        ) s
    where row_number = num_days
$BODY$;

Is there any fix/modification or alternative to this logic to make it work for both positive and negative number of days?



Read more here: https://stackoverflow.com/questions/64901254/add-business-days-in-date-in-postgresql-function

Content Attribution

This content was originally published by hotcoder at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: