Description:
I should have had a DATE_ADD which working with WORKDAYS!
For example: DATE_ADD('2010-01-12',INTERVAL 10 WORKDAY).
I had to see there is no such UNIT WORKDAY only DAY.
I had to write a function:
It seems simple but I worked on it very a lot of time:
( Of course without FOR or WHILE cycle! )
CREATE DEFINER=`root`@`%` FUNCTION `f_addworkday`($startdate datetime,$workday int) RETURNS datetime
DETERMINISTIC
BEGIN
declare $c int default 0;
declare $w int;
declare $r datetime;
declare $m int;
set $startdate=date_add($startdate,interval elt(weekday($startdate)+1,0,0,0,0,0,2,1) day);
/* if $startdate is Weekend then I set it to Monday! */
set $w=truncate($workday/5,0)*7;
set $m=$workday%5;
if weekday($startdate)+$m > 4 then
set $c=2;
elseif weekday($startdate)+$m<0 then /* this is only if $workday is negative... ! */
set $c=-2;
end if;
set $r=date_add($startdate,interval $w+$m+$c day);
return $r;
END
Using example:
SELECT f_addworkday('2010-01-12',10)
Result:
2010-01-26 00:00:00
Regards
npongrac
How to repeat:
(Feature request and tip.)
Description: I should have had a DATE_ADD which working with WORKDAYS! For example: DATE_ADD('2010-01-12',INTERVAL 10 WORKDAY). I had to see there is no such UNIT WORKDAY only DAY. I had to write a function: It seems simple but I worked on it very a lot of time: ( Of course without FOR or WHILE cycle! ) CREATE DEFINER=`root`@`%` FUNCTION `f_addworkday`($startdate datetime,$workday int) RETURNS datetime DETERMINISTIC BEGIN declare $c int default 0; declare $w int; declare $r datetime; declare $m int; set $startdate=date_add($startdate,interval elt(weekday($startdate)+1,0,0,0,0,0,2,1) day); /* if $startdate is Weekend then I set it to Monday! */ set $w=truncate($workday/5,0)*7; set $m=$workday%5; if weekday($startdate)+$m > 4 then set $c=2; elseif weekday($startdate)+$m<0 then /* this is only if $workday is negative... ! */ set $c=-2; end if; set $r=date_add($startdate,interval $w+$m+$c day); return $r; END Using example: SELECT f_addworkday('2010-01-12',10) Result: 2010-01-26 00:00:00 Regards npongrac How to repeat: (Feature request and tip.)