DELIMITER $$
CREATE FUNCTION scoreboard.get_week_from_date(game_date DATE)
RETURNS TINYINT(2) UNSIGNED
DETERMINISTIC
BEGIN
    DECLARE week TINYINT(2) UNSIGNED;
    DECLARE `year` SMALLINT(4) UNSIGNED;
    DECLARE first_monday DATE;
    DECLARE tuesday DATE;

    SET `year` = YEAR(game_date);
    IF game_date <= DATE(CONCAT(`year`,'-06-30')) THEN
        SET week = 17;
        RETURN week;
    END IF;

    SET first_monday = DATE(CONCAT(`year`,'-09-01'));
    SET tuesday = DATE_ADD(first_monday, INTERVAL (1 - WEEKDAY(first_monday) + 7) % 7 DAY);

    IF game_date < tuesday THEN
        SET week = IF(game_date < (tuesday - INTERVAL 7 DAY),0,1);
        RETURN week;
    END IF;

    SET week = LEAST(17,DATEDIFF(game_date,tuesday) DIV 7 + 2);
    RETURN week;
END $$
DELIMITER ;
