어느 시작일 부터 특정 기간 범위를 지정했을 때
오늘에 포함되는 특정 기간의 시작일과 종료일을 구해보자
DELIMITER $$
USE `testdb`$$
DROP FUNCTION IF EXISTS `ufn_start_end_date`$$
CREATE DEFINER=`user`@`%` FUNCTION `ufn_start_end_date`(
`iStartDate` DATE,
`iPeriod` SMALLINT,
`iUnit` VARCHAR(2),
`iType` CHAR(1)
) RETURNS DATE
BEGIN
DECLARE mStartDate DATE;
DECLARE mEndDate DATE;
IF iStartDate > CURRENT_DATE THEN
RETURN NULL;
END IF;
IF iUnit = 'W' THEN
SET mStartDate = iStartDate;
SET mEndDate = DATE_SUB(DATE_ADD(mStartDate, INTERVAL iPeriod WEEK), INTERVAL 1 DAY);
ELSE
SET mStartDate = iStartDate;
SET mEndDate = DATE_SUB(DATE_ADD(mStartDate, INTERVAL iPeriod MONTH), INTERVAL 1 DAY);
END IF;
mloop : WHILE 1 = 1 DO
IF CURRENT_DATE BETWEEN mStartDate AND mEndDate THEN
LEAVE mloop;
END IF;
IF iUnit = 'W' THEN
SET mStartDate = DATE_ADD(mStartDate, INTERVAL iPeriod WEEK);
SET mEndDate = DATE_ADD(mEndDate, INTERVAL iPeriod WEEK);
ELSE
SET mStartDate = DATE_ADD(mStartDate, INTERVAL iPeriod MONTH);
SET mEndDate = DATE_ADD(mEndDate, INTERVAL iPeriod MONTH);
END IF;
END WHILE mloop;
IF iType = 'S' THEN
RETURN mStartDate;
ELSE
RETURN mEndDate;
END IF;
END$$
DELIMITER ;
