How to detect a leap year with Oracle Oracle Tips by Burleson Consulting January 15, 2008

Question:  How do I make Oracle detect a leap year?  Do I need to use PL/SQL to find a leap year, or is there a Oracxle built-in function to detect a leap year?

Answer:  A year that is designated as a “leap year”, is very special, with a 29th day in February.  A year is considered a leap year if the year is divisible by 4 but not divisible by 100 unless also divisible by 400.   Now, Oracle does not directly detect a leap year, but there are several way to detect a leap year using PL/SQL, and you can make your leap_year_test code callable from SQL, just like a BIF.

Goran Udovicic has published a PL/SQL function to detect a leap year, but Ken Stevens of the US DOE has this created a more elegant example of a function to test whether a year is a leap year: 

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;

If you like Oracle tuning, you might enjoy my book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.