CODEWITHSUNDEEP
mysqlsqlweekday
penCsharpener
penCsharpener

Reputation: 449

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

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.

Upvotes: 1

Views: 438

Answers (3)

penCsharpener
penCsharpener

Reputation: 449

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 ;

Upvotes: 0

Sahal
Sahal

Reputation: 298

Considering a week starts from Monday. Hope this helps:

DELIMITER |
CREATE FUNCTION IS_BETWEEN_WEEKDAYS(d DATETIME, s INT, e INT)
RETURNS BOOLEAN DETERMINISTIC
BEGIN
  DECLARE weekdayToCheck INT;
  DECLARE result TINYINT(1);
  SET weekdayToCheck =  WEEKDAY(d);
  SET result = 0;
  IF(weekdayToCheck > s AND weekdayToCheck < e) THEN
    SET result = 1;
  ELSEIF (s > e AND weekdayToCheck < s AND weekdayToCheck < e) THEN
    SET result = 1;
  END IF;

RETURN result;
END; |

DELIMITER ;

Upvotes: 1

Joakim Danielson
Joakim Danielson

Reputation: 51892

Not sure what your definition of "between" is but here I used larger than and smaller than

CREATE FUNCTION IS_BETWEEN_WEEKDAYS(d DATETIME, s INT, e INT)
RETURNS BOOLEAN DETERMINISTIC
BEGIN
    DECLARE weekdayToCheck INT;
    DECLARE result INT;
    SET weekdayToCheck = WEEKDAY(d);
    SET result = 0;

    IF (s < e) THEN 
       IF  (weekdayToCheck > s AND weekdayToCheck < e) THEN SET result = 1;
       END IF;
    END IF;

    IF (s > e) THEN
       IF (weekdayToCheck > s OR weekdayToCheck < e) THEN SET result = 1;
       END IF;        
    END IF;

    RETURN result; 
END;

Perhaps all the if/else clause can be shortened somewhat.

Upvotes: 2

Related Questions