Detect a Leap Year with PL/SQL

We have seen some very complicated ways to detect leap year. Follow functions are probably the most elegant method we can find:

function DATE_LEAP_YEAR_IS
--------------------------------------------------------------------------------
-- This function is to return if a given year is leap year or not.
-- This is 1 of 2 overloaded functions.
-- Input: Parameter 1: The given year in number.
-- Output: Return: The result in single varchar2, 'Y' is leap year, 'N' is not.
-- Authers: Ken Stevens, Lee Howe, Jane Stone & Howard Stone
--------------------------------------------------------------------------------
        (      nYr in number
        )      return varchar2
as v_day varchar2(2);
begin
        v_day := to_char(last_day(to_date(to_char(nYr)||'/02/01', 'YYYY/MM/DD')), 'DD');
        if v_day = '29' -- if v_day = 29 then it must be a leap year, return TRUE.
        then return 'Y';
        else return 'N';
        end if;
end DATE_LEAP_YEAR_IS;

function DATE_LEAP_YEAR_IS
--------------------------------------------------------------------------------
-- This function is to return if a given year is leap year or not.
-- This is 1 of 2 overloaded functions.
-- Input: Parameter 1: The given year in date.
-- Output: Return: The result in single varchar2, 'Y' is leap year, 'N' is not.
-- Authers: Ken Stevens, Lee Howe, Jane Stone & Howard Stone
--------------------------------------------------------------------------------
        (      dDate in date
        )      return      varchar2
as v_day varchar2(2);
begin
        v_day := to_char(last_day(to_date(to_char(extract(year from dDate))||'/02/01', 'YYYY/MM/DD')), 'DD');
        if v_day = '29' -- if v_day = 29 then it must be a leap year, return TRUE.
        then return 'Y';
        else return 'N';
        end if;
end DATE_LEAP_YEAR_IS;

Following is Don Burleson’s scripts:

create or replace function IS_LEAP_YEAR (nYr in number) return boolean is
v_day varchar2(2);
begin
    select to_char(last_day(to_date( '01-FEB-'|| to_char(nYr), 'DD-MON-YYYY')), 'DD') into v_day from dual;
    if v_day = '29' then -- if v_day = 29 then it must be a leap year, return TRUE
        return TRUE;
    else
        return FALSE;    -- otherwise year is not a leap year, return false
    end if;
end;

http://www.dba-oracle.com/t_detect_leap_year_function.htm
http://www.dba-oracle.com/oracle_news/2005_8_23_function_detect_leap_year.htm

No comments:

Post a Comment

Labels