Saturday, August 26, 2006

MySQL function: months_between

After directing a Devshed poster looking for a way to compute the number of months between two dates to the manual I decided to turn the solution posted in user comments by Isaac Shepard into a function, here it is:

  1. DELIMITER $$
  2. DROP FUNCTION IF EXISTS `test`.`months_between` $$
  3. CREATE FUNCTION `test`.`months_between` (date_start DATE, date_end DATE) RETURNS INT
  4. BEGIN
  5. SELECT IF((((YEAR(date_end) - 1) * 12 + MONTH(date_end)) - ((YEAR(date_start) - 1) * 12 + MONTH(date_start))) > 0, (((YEAR(date_end) - 1) * 12 + MONTH(date_end)) - ((YEAR(date_start) - 1) * 12 + MONTH(date_start))) - (MID(date_end, 9, 2) < style="color: rgb(102, 204, 102);">(date_start, 9, 2)), IF((((YEAR(date_end) - 1) * 12 + MONTH(date_end)) - ((YEAR(date_start) - 1) * 12 + MONTH(date_start))) < 0, (((YEAR(date_end) - 1) * 12 + MONTH(date_end)) - ((YEAR(date_start) - 1) * 12 + MONTH(date_start))) + (MID(date_start, 9, 2) < style="color: rgb(102, 204, 102);">(date_end, 9, 2)), (((YEAR(date_end) - 1) * 12 + MONTH(date_end)) - ((YEAR(date_start) - 1) * 12 + MONTH(date_start))))) INTO @num_months;
  6. RETURN @num_months;
  7. END $$
  8. DELIMITER ;

Hope it helps!

2 comments:

Jay Pipes said...

Hi! Great function! Would you consider adding it to MySQL Forge's snippet library? http://forge.mysql.com/snippets/

pabloj said...

Snippet added http://forge.mysql.com/snippets/view.php?id=40