2 Replies Latest reply on Jan 14, 2007 3:06 PM by Eduardo Legatti

    Leap Year

    A.MohammedRafi
      Hi,

      Is there any simple standard oracle function to calculate leap year.

      thanks
        • 1. Re: Leap Year
          Nicolas.Gasparotto
          No built-in function for that.
          But, check what's the last day of february should be enough :
          SQL> var my_year varchar2(4)
          SQL> exec :my_year:=2004

          PL/SQL procedure successfully completed.

          SQL> select decode(to_char(last_day(to_date('02'||:my_year,'MMYYYY')),'DD')
            2                ,29,'LEAP YEAR'
            3                ,'NO LEAP YEAR')
            4* from dual
          SQL> /

          DECODE(TO_CH
          ------------
          LEAP YEAR

          SQL> exec :my_year:=2006

          PL/SQL procedure successfully completed.

          SQL> l
            1  select decode(to_char(last_day(to_date('02'||:my_year,'MMYYYY')),'DD')
            2                ,29,'LEAP YEAR'
            3                ,'NO LEAP YEAR')
            4* from dual
          SQL> /

          DECODE(TO_CH
          ------------
          NO LEAP YEAR
          Or, mathematical :
          SQL> exec :my_year:=2004

          PL/SQL procedure successfully completed.

          SQL> select case when mod(:my_year,400)=0 or (mod(:my_year,4)=0 and mod(:my_year,100)!=0)
            2         then 'LEAP YEAR'
            3         else 'NO LEAP YEAR' end YEAR
            4  from dual;

          YEAR
          ------------
          LEAP YEAR

          SQL> exec :my_year:=2006

          PL/SQL procedure successfully completed.

          SQL> select case when mod(:my_year,400)=0 or (mod(:my_year,4)=0 and mod(:my_year,100)!=0)
            2         then 'LEAP YEAR'
            3         else 'NO LEAP YEAR' end YEAR
            4  from dual;

          YEAR
          ------------
          NO LEAP YEAR

          SQL>
          Nicolas.
          • 2. Re: Leap Year
            Eduardo Legatti
            Hi,

            In addition, you can also create a function.... just example:
            SQL> create or replace function leapyear (vyear in number) return number
              2  is
              3    vremain1 number(5,2);
              4    vremain2 number(5,2);
              5    vremain3 number(5,2);
              6  begin
              7    vremain1 := mod(vyear,4);
              8    vremain2 := mod(vyear,100);
              9    vremain3 := mod(vyear,400);
            10
            11    if ((vremain1 = 0 and vremain2 <> 0 ) or vremain3 = 0) then
            12    dbms_output.put_line(to_char(vyear) || ' is a leap year');
            13    return 1;
            14    else
            15    dbms_output.put_line (to_char(vyear) || ' is not a leap year');
            16    return 0;
            17    end if;
            18  end;
            19  /

            Function created.

            SQL> set serveroutput on;

            SQL> select leapyear(2001) from dual;

            LEAPYEAR(2001)
            --------------
                         0

            2001 is not a leap year
            SQL>
            SQL> select leapyear(2004) from dual;

            LEAPYEAR(2004)
            --------------
                         1

            2004 is a leap year
            SQL>
            SQL> select leapyear(2007) from dual;

            LEAPYEAR(2007)
            --------------
                         0

            2007 is not a leap year
            SQL> select leapyear(2008) from dual;

            LEAPYEAR(2008)
            --------------
                         1

            2008 is a leap year
            SQL>
            Cheers