MySQL: find out whether weekday of date is between two weekdays with SQL

Question

I have this vb.NET code and would like to write a function in MySQL that does the same thing.

Public Function IsWeekdayTimeInRange(DateToCheck As DateTime, StartDayOfWeek As DayOfWeek,
                                                              EndDayOfWeek As DayOfWeek) As Boolean

    Dim WeekdaysInRange As New List(Of DayOfWeek)
    WeekdaysInRange.Add(StartDayOfWeek)
    Dim i As Integer = StartDayOfWeek
    While i <> EndDayOfWeek
        i += 1
        i = If(i = 7, 0, i)
        WeekdaysInRange.Add(i)
    End While

    Return WeekdaysInRange.Contains(DateToCheck.DayOfWeek)
End Function

In MySQL I'm currently at this point but since I've never written a SQL function I need some help with this.

DELIMITER |
CREATE FUNCTION IS_BETWEEN_WEEKDAYS(d DATETIME, s INT, e INT)
RETURNS BOOLEAN DETERMINISTIC
BEGIN
    DECLARE weekdayToCheck INT;
    DECLARE result BOOLEAN;
    DECLARE i INT;
    SET result = false;
    SET weekdayToCheck = WEEKDAY(d);
    SET i = s;
    WHILE(i <> e) DO

        /* more logic here */

    END WHILE;

    RETURN result;
END; |

DELIMITER ;

Edit: Since I now learnt that MySQL has Weekday(Monday) = 0 my functions also require conversion to Weekday(Sunday) = 0 because my data use that format.

Answer 1

This works now. It probably can be more concise but it helped me to think it through.

DELIMITER |
CREATE FUNCTION IS_BETWEEN_WEEKDAYS(d DATETIME, s INT, e INT, FirstDayOfWeekIsSunday BOOLEAN)
RETURNS BOOLEAN DETERMINISTIC
BEGIN
    DECLARE weekdayToCheck INT;
    DECLARE result BOOLEAN;
    DECLARE i INT;
    SET result = false;
    SET weekdayToCheck = WEEKDAY(d);
    SET i = s;

    IF FirstDayOfWeekIsSunday = TRUE THEN
        SET weekdayToCheck = weekdayToCheck + 1;
        IF weekdayToCheck = 7 THEN
            SET weekdayToCheck = 0;
        END IF;
    END IF;

    IF weekdayToCheck = s THEN
        SET result = true;
    END IF;

    WHILE(i <> e) DO

        SET i = i + 1;

        IF i = 7 THEN
            SET i = 0;
        END IF;

        IF weekdayToCheck = i THEN 
            SET result = true;
        END IF;

    END WHILE;

    RETURN result;
END; |

DELIMITER ;

Edit: fixed a problem with the order in the loop. Also added option to function to process s and e should they assume the week starts with Sunday. Edit: tested the above and it works. Also create a function that can check whether for example a DateTime is in between Tuesday, 3pm and Thursday, 2am.

DELIMITER |
CREATE FUNCTION IS_BETWEEN_WEEKDAYS_AND_TIME(d DATETIME, s INT, starttime TIME, e INT, endtime TIME, FirstDayOfWeekIsSunday BOOLEAN)
RETURNS BOOLEAN DETERMINISTIC
BEGIN
    DECLARE weekdayToCheck INT;
    DECLARE result BOOLEAN;
    DECLARE i INT;
    DECLARE dTime TIME;
    SET result = false;
    SET weekdayToCheck = WEEKDAY(d);
    SET i = s;
    SET dTime = DATE_FORMAT(d, '%H:%i:%s');

    IF FirstDayOfWeekIsSunday = TRUE THEN
        SET weekdayToCheck = weekdayToCheck + 1;
        IF weekdayToCheck = 7 THEN
            SET weekdayToCheck = 0;
        END IF;
    END IF;

    IF weekdayToCheck = s THEN 
        IF ((weekdayToCheck = s AND dTime > starttime) OR (weekdayToCheck = e AND dTime < endtime) OR (weekdayToCheck <> s AND weekdayToCheck <> e)) THEN
            SET result = true;
        END IF;
    END IF;

    WHILE(i <> e) DO

        SET i = i + 1;

        IF i = 7 THEN
            SET i = 0;
        END IF;

        IF weekdayToCheck = i THEN 
            IF ((weekdayToCheck = s AND dTime > starttime) OR (weekdayToCheck = e AND dTime < endtime) OR (weekdayToCheck <> s AND weekdayToCheck <> e)) THEN
                SET result = true;
            END IF;
        END IF;

    END WHILE;

    RETURN result;
END; |

DELIMITER ;