Untitled

-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$
 
CREATE DEFINER=`stud02sa`@`%` FUNCTION `WEEKENDING`( week_string CHAR(19), week_day INT, ending_hour INT ) RETURNS char(19) CHARSET utf8
    READS SQL DATA
BEGIN
    -- Declare Variables
    DECLARE week_day_difference INT;
    DECLARE hour_day_difference INT;
 
    -- Get the week-day difference
    SET week_day_difference = WEEKDAY( week_string ) - week_day + 1;
 
    -- Prevent the date from going backwards
    IF( week_day_difference > 0 ) THEN
        SET week_day_difference = week_day_difference - 7;
    END IF;
 
    -- Get the hour-day difference
    SET hour_day_difference = HOUR( week_string ) - ending_hour;
 
    -- If both are 0 (the exact week ending hour), then we need to move it to the next week
    IF( week_day_difference = 0 AND hour_day_difference = 0 ) THEN
        SET week_day_difference = week_day_difference -7;
    END IF;
 
    -- Return new date
    RETURN DATE_FORMAT( DATE_SUB( week_string, INTERVAL ( week_day_difference * 24 ) + hour_day_difference HOUR ), '%Y-%m-%d %H:00:00' );
 END