Skip to content

Instantly share code, notes, and snippets.

@gerardolima
Last active October 8, 2019 09:18
Show Gist options
  • Select an option

  • Save gerardolima/f4763c75f2ebdb1f4397c85ed467632c to your computer and use it in GitHub Desktop.

Select an option

Save gerardolima/f4763c75f2ebdb1f4397c85ed467632c to your computer and use it in GitHub Desktop.
Difference between 2 dates, in week boundaries, for MySql
delimiter //
CREATE FUNCTION f_weekdiff(expr1 DATETIME, expr2 DATETIME)
RETURNS INTEGER
COMMENT 'Returns the number of week boundaries between expr1 and expr2. Example: f_weekdiff(\'2019-10-07\', \'2019-09-30\'); == 1'
DETERMINISTIC
LANGUAGE SQL
BEGIN
SET expr1 = ADDDATE(expr1, -1 * WEEKDAY(expr1)); -- "moves" expr1 to the beggining of its week
SET expr2 = ADDDATE(expr2, -1 * WEEKDAY(expr2)); -- "moves" expr2 to the beggining of its week
RETURN DATEDIFF(expr1, expr2) / 7;
END;
delimiter ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment