Hi everyone,
I would like to share this function related with BIRT:
--
-- Routine DDL
--
DELIMITER $$
CREATE DEFINER=`root`
@`localhost` FUNCTION `BIRT_DateOfWeek`(origine DATE, pos INT) RETURNS date
DETERMINISTIC
BEGIN
DECLARE target DATE;
DECLARE i INTEGER;
DECLARE C1 BOOLEAN DEFAULT FALSE;
DECLARE C2 BOOLEAN DEFAULT FALSE;
DECLARE C3 BOOLEAN DEFAULT FALSE;
SET i = WEEKDAY(origine);
SET target = origine;
IF ((pos >= 0) AND (pos <= 6)) THEN
IF ((pos = 0) AND (C2 = FALSE)) THEN
IF (i = pos) THEN
SET pos = pos + i;
END IF;
IF (i > pos) THEN
SET pos = pos - i;
END IF;
SET C2 = TRUE;
END IF;
IF ((pos = 6) AND (C3 = FALSE)) THEN
IF ((i < pos) OR (i = pos)) THEN
SET pos = pos - i;
END IF;
SET C3 = TRUE;
END IF;
IF ((pos > 0) AND (pos < 6) AND (C1 = FALSE) AND (C2 = FALSE) AND (C3 = FALSE)) THEN
SET pos = pos - i;
SET C1 = TRUE;
END IF;
SET target = DATE(CONCAT(YEAR(origine),'-',MONTH(origine),'-',DAY(origine) + pos));
END IF;
RETURN target;
END
This function gives the first day of a week, last day of a week or any date in a week.
You have two parameters:
- origine: the date where you start in a week
- pos: from 0 (1st day) to 6 (last day) or between 1 and 5 for any date you want to get
Hope this help,
Cheers,
Christophe
