Blog

CREATE CUSTOM FUNCTION IN MYSQL , Get count of weekdays between 2 dates

CREATE CUSTOM FUNCTION IN MYSQL , Get count of weekdays between 2 datesWe will be able to create as many functions as we can in using Create Functions. This will be Helpful when you need to perform a Calculations. Here I have an Example to get the count of weekdays between 2 dates.

Get Count days by adding days to the current

DELIMITER $$
DROP FUNCTION IF EXISTS `getCountdays` $$

CREATE FUNCTION `getCountdays` (start_date DATE, date_count INT)

RETURNS INT DETERMINISTIC
BEGIN

DECLARE remainder INT;
DECLARE date_sequence DATE;
DECLARE dayz INT;
DECLARE week_day INT;

SET remainder = 0;
SET dayz = 0;

read_loop: LOOP
IF remainder= date_count THEN
LEAVE read_loop;
END IF;
SELECT DATE(DATE_ADD(start_date, INTERVAL 1 DAY)) INTO date_sequence;
SELECT WEEKDAY(DATE_ADD(start_date, INTERVAL 1 DAY)) INTO week_day;

IF week_day < 5 THEN
SET dayz = dayz + 1;
END IF;

SET remainder = remainder + 1;
SET start_date = date_sequence;

END LOOP;

RETURN dayz;

END $$
DELIMITER ;

SELECT getCountdays(’2013-02-20′,30); // 30 is the days you need to add with the current date

With a slight change in this Example you can create function for between two dates

This Post Has 0 Comments

Leave A Reply