Calculate number of day : Function « Procedure Function « SQL / MySQL






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

Calculate number of day

       
mysql>
mysql> delimiter $$
mysql>
mysql> CREATE FUNCTION NUMBER_OF_DAYS
    ->    (START_DATE DATE,
    ->     END_DATE DATE)
    ->     RETURNS INTEGER
    -> BEGIN
    ->    DECLARE DAYS INTEGER;
    ->    DECLARE NEXT_DATE, PREVIOUS_DATE DATE;
    ->    SET DAYS = 0;
    ->    SET NEXT_DATE = START_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;
    ->    RETURN DAYS;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql>








Related examples in the same category

1.Creating Functions
2.A function that takes a parameter, performs an operation using an SQL function, and returns the result
3.Single statement function
4.Nested function call
5.Using buildin function in user-defined function
6.To remove a stored function, use the DROP command
7.Create a procedure for 'READS SQL DATA'
8.Math calculation in a function
9.Function to shorten a string