Find out the age with procedure : Procedure « Procedure Function « SQL / MySQL

1. SQL / MySQL
2. Procedure Function
3. Procedure

Find out the age with procedure

       

DELIMITER $$

CREATE PROCEDURE AGE
   (IN  START_DATE  DATE,
    IN  END_DATE    DATE,
    OUT YEARS       INTEGER,
    OUT MONTHS      INTEGER,
    OUT DAYS        INTEGER)
BEGIN
   DECLARE NEXT_DATE, PREVIOUS_DATE DATE;
   SET YEARS = 0;
   SET PREVIOUS_DATE = START_DATE;
   SET NEXT_DATE = START_DATE + INTERVAL 1 YEAR;
   WHILE NEXT_DATE < END_DATE DO
      SET YEARS = YEARS + 1;
      SET PREVIOUS_DATE = NEXT_DATE;
      SET NEXT_DATE = NEXT_DATE + INTERVAL 1 YEAR;
   END WHILE;
   SET MONTHS = 0;
   SET NEXT_DATE = PREVIOUS_DATE + INTERVAL 1 MONTH;
   WHILE NEXT_DATE < END_DATE DO
      SET MONTHS = MONTHS + 1;
      SET PREVIOUS_DATE = NEXT_DATE;
      SET NEXT_DATE = NEXT_DATE + INTERVAL 1 MONTH;
   END WHILE;
   SET DAYS = 0;
   SET NEXT_DATE = PREVIOUS_DATE + INTERVAL 1 DAY;
   WHILE NEXT_DATE <= END_DATE DO
      SET DAYS = DAYS + 1;
      SET PREVIOUS_DATE = NEXT_DATE;
      SET NEXT_DATE = NEXT_DATE + INTERVAL 1 DAY;
   END WHILE;
END$$

DELIMITER ;

SET @START = '1991-01-12'
;
SET @END = '1999-07-09'
;
CALL AGE (@START, @END, @YEAR, @MONTH, @DAY)
;
SELECT @START, @END, @YEAR, @MONTH, @DAY;

   
    
    
    
    
    
    
  

Related examples in the same category