Josh Berkus wrote:> Folks,>> Hey, I need to write a date calculation function that calculates the> date after a number
of*workdays* from a specific date. I pretty> much have the "skip holidays" part nailed down, but I don't have a>
reallygood way to skip all weekends in the caluclation. Here's the> ideas I've come up with:
How about this (a bit ugly, but I think it does what you want -- minus
the holidays, which you said you already have figured out):
create or replace function get_future_work_day(timestamp, int) returns timestamp as '
select case when extract(dow from future_work_date) = 6 then future_work_date + ''2 days'' when
extract(dowfrom future_work_date) = 0 then future_work_date + ''1 day'' else
future_work_date end
from ( select $1 + (($2 / 5)::text || '' weeks'') + (($2 % 5)::text || '' days'')
asfuture_work_date ) as t1
' language sql;
CREATE
testslv=# select get_future_work_day('2002-06-20',27); get_future_work_day
------------------------ 2002-07-29 00:00:00-07
(1 row)
HTH,
Joe