How to check the Leap Year using Function in Oracle SQL

Question

Do i need to write in PL/SQL or is it possible in SQL query?

Answer 1

CREATE OR REPLACE FUNCTION isLeapYear(i_year NUMBER) RETURN boolean AS
BEGIN
IF mod(i_year, 400) = 0 OR ( mod(i_year, 4) = 0 AND mod(i_year, 100) != 0) THEN
return TRUE;
ELSE
return FALSE;
END IF;
END;
---------------------------- Calling the function in a PL/SQL program -----------------------------------
BEGIN
IF isLeapYear(&year) THEN
dbms_output.put_line('Yes, it is a leap year');
ELSE
dbms_output.put_line('No, it is not a leap year');
END IF;
END;

Answer 2

create function leap_year_or_not(i_year IN NUMBER)
RETURN VARCHAR2
IS
l_var VARCHAR2(20);
BEGIN
IF TO_CHAR(LAST_DAY(TO_DATE('01/02/'||i_year, 'dd/mm/yyyy')), 'DD') = 29 THEN
l_var := 'Leap Year';
ELSE
l_var := 'Not Leap Year';
END IF;
RETURN l_var;
END;
/

Comments

create or replace function leap_year_or_not(i_year IN NUMBER)
RETURN VARCHAR2
IS
l_var VARCHAR2(20);
BEGIN
IF i_year mod 4=0 then

l_var := 'Leap Year';
ELSE
l_var := 'Not Leap Year';
END IF;
RETURN l_var;
END;
/