how to find the leap year in a sql statement using while loop

CREATE FUNCTION list_leap_years (first_year INT, last_year INT)
RETURNS TEXT 
DETERMINISTIC
BEGIN
-- declare the variable where leap years will be collected for output
DECLARE output TEXT DEFAULT '';
WHILE first_year <= last_year DO
    -- test does the next day for "Feb, 28" is "Feb, 29"
    IF DAY(CONCAT(first_year, '-02-28') + INTERVAL 1 DAY) = 29 THEN
        -- if true - add this year to the list
        SET output := CONCAT(output, ',', first_year);
    END IF;
    -- increase the year for testing on the next cycle
    SET first_year := first_year + 1;
END WHILE;
-- return collected years
RETURN SUBSTRING(output FROM 2);
END

fiddle

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top