mysql stored funtion usage
- by shikhar
I just wrote a stored function to calculate the working days between two dates.
This works
select
CountWeekDays('2010-03-07','2010-04-07')
This doesn't work
select
CountWeekDays(o.order_date,o.created_date)
from orders o;
Any idea how to make this one work ??
function definition
delimiter $$;
CREATE FUNCTION CountWeekDays (sdate
VARCHAR(50), edate VARCHAR(50))
RETURNS INT
BEGIN
DECLARE wdays, tdiff, counter, thisday
smallint;
DECLARE newdate DATE;
SET newdate := sdate;
SET wdays = 0;
if DATEDIFF(edate, sdate) = 0 THEN
RETURN 1; END IF;
if DATEDIFF(edate, sdate) < 0 THEN
RETURN 0; END IF;
label1: LOOP
SET thisday = DAYOFWEEK(newdate);
IF thisday BETWEEN 2 AND 6 THEN SET
wdays := wdays + 1; END IF;
SET newdate = DATE_ADD(newdate,
INTERVAL 1 DAY);
IF DATEDIFF(edate, newdate) < 0 THEN
LEAVE label1; END IF;
END LOOP label1;
RETURN wdays;
END
