MySQL is very rich with built in function as it have thousands of functions. But apart from this rich library sometimes we need to create some custom function to get the task done. Today I encountered same kind of situation. I need a function which can return no. of working days between two dates and it should also return the weekend days between two days. Here is the solution for this problem. I created a user defined function to calculate working dates between two dates.
In one week, there are 7 days but working days are Monday to Friday.
- This function can calculate Days difference between two days.
- Working days between two days (Excluding Saturday & Sunday).
- Weekend days between two days (Saturday & Sunday).
DELIMITER $$
DROP FUNCTION IF EXISTS `DBName`.`getWorkingday`$$
CREATE FUNCTION `getWorkingday`(d1 datetime,d2 datetime, retType varchar(20)) RETURNS varchar(255) CHARSET utf8
BEGIN
DECLARE dow1, dow2,daydiff,workdays, weekenddays, retdays,hourdiff INT;
declare newstrt_dt datetime;
SELECT dd.iDiff, dd.iDiff - dd.iWeekEndDays AS iWorkDays, dd.iWeekEndDays into daydiff, workdays, weekenddays
FROM (
SELECT
dd.iDiff,
((dd.iWeeks * 2) +
IF(dd.iSatDiff >= 0 AND dd.iSatDiff < dd.iDays, 1, 0) +
IF (dd.iSunDiff >= 0 AND dd.iSunDiff < dd.iDays, 1, 0)) AS iWeekEndDays
FROM (
SELECT dd.iDiff, FLOOR(dd.iDiff / 7) AS iWeeks, dd.iDiff % 7 iDays, 5 - dd.iStartDay AS iSatDiff, 6 - dd.iStartDay AS iSunDiff
FROM (
SELECT
1 + DATEDIFF(d2, d1) AS iDiff,
WEEKDAY(d1) AS iStartDay
) AS dd
) AS dd
) AS dd ;
if(retType = 'day_diffs') then
set retdays = daydiff;
elseif(retType = 'work_days') then
set retdays = workdays;
elseif(retType = 'weekend_days') then
set retdays = weekenddays;
end if;
RETURN retdays;
END$$
DELIMITER ;
Normal Days difference
select getWorkingday('2012-09-01 15:43:59','2012-09-20 15:43:59','day_diffs') as result;
result: 20
Working days between two dates (Excluding Saturdays & Sundays)
select getWorkingday('2012-09-01 15:43:59','2012-09-20 15:43:59','work_days') as result;
result: 14
Weekends between two days (Saturdays & Sundays)
select getWorkingday('2012-09-01 15:43:59','2012-09-10 15:43:59','weekend_days') as result;
result: 6
This site was very usefull to me, it was just what i needed. Thanks!
ReplyDeleteNo matter if some one searches for his necessary thing, thus he/she wants to be available that in detail,
ReplyDeleteso that thing is maintained over here.
my blog - seo (seofornown4eva.com)
Very good info. Lucky me I recently found your blog
ReplyDeleteby accident (stumbleupon). I have book-marked it for later!
My weblog :: client.bani24.ro