oracle 闰年判断

//闰年最直接的判断是:能被4整除且不能被100整除，或者能被400整除的年份 create or replace procedure pro_leap_year(year_in in number) as v_mod1 number(4) :=mod(year_in,4); v_mod2 number(4) :=mod(year_in,100); v_mod3 number(4) :=mod(year_in,400); begin if ((v_mod1=0 and v_mod2<>0) or v_mod3=0) then dbms_output.put_line(year_in||' is a leap year'); else dbms_output.put_line(year_in||' is not a leap year'); end if; exception when others then dbms_output.put_line(sqlerrm); end pro_leap_year; / SQL> exec pro_leap_year(2010); 2010 is not a leap year PL/SQL procedure successfully completed // SQL> exec pro_leap_year(2000); 2000 is a leap year PL/SQL procedure successfully completed // //关于闰年平年，下面有一个比较全面的解释： //在公历（格里历）纪年中，有闰日的年份叫闰年，一般年份365天，闰年为366天。 //由于地球绕太阳运行周期为365天5小时48分46秒（合365.24219天）即一回归年，公历把一年定为365天。 //所余下的时间约为四年累计一天，加在二月里，所以平常年份每年365天，二月为28天，闰年为366天，二月为29天。 //因此，每400年中有97个闰年，闰年在2月末增加一天，闰年366天。 //闰年的计算方法：公元纪年的年数可以被四整除，即为闰年； //被100整除而不能被400整除为平年；被100整除也可被400整除的为闰年。 //如2000年是闰年，而1900年不是。 // //我们所关心的是怎么样计算指定的一个年份是否是闰年： //从上面的描述中，我们知道： //如果一年中2月份有29天，那么这一年就是闰年，否则是平年 SQL> drop procedure pro_leap_year; Procedure dropped // create or replace procedure pro_leap_year(year_in in number) as v_year_in varchar2(10) :=to_char(year_in)||'0229'; v_date date; begin //这里将拼接的字符串转换为日期，并赋值给一个日期类型的变量， //其实就是为了和日期类型进行比较，隐式的比较，比较你输入的年份中2月是否含有29日这一天 v_date :=to_date(v_year_in,'yyyy-mm-dd'); dbms_output.put_line(year_in||' is a leap year'); exception when others then dbms_output.put_line(year_in||' is not a leap year'); end pro_leap_year; / SQL> exec pro_leap_year(2010); 2010 is not a leap year PL/SQL procedure successfully completed // SQL> exec pro_leap_year(2000); 2000 is a leap year PL/SQL procedure successfully completed // SQL> exec pro_leap_year(2005); 2005 is not a leap year PL/SQL procedure successfully completed // SQL> exec pro_leap_year(2100); 2100 is not a leap year PL/SQL procedure successfully completed // //我们也可以用一条select语句来判断： SQL> select case 2 when to_char(last_day(to_date(&year||'02','yyyymm')),'dd')='29' 3 then 'the year you input is a leap year' 4 else 'the year you input is not a leap year' 5 end is_leap_year 6 from dual; Enter value for year: 2050 old 2: when to_char(last_day(to_date(&year||'02','yyyymm')),'dd')='29' new 2: when to_char(last_day(to_date(2050||'02','yyyymm')),'dd')='29' IS_LEAP_YEAR ------------------------------------- the year you input is not a leap year // SQL> / Enter value for year: 2000 old 2: when to_char(last_day(to_date(&year||'02','yyyymm')),'dd')='29' new 2: when to_char(last_day(to_date(2000||'02','yyyymm')),'dd')='29' IS_LEAP_YEAR --------------------------------- the year you input is a leap year // SQL> / Enter value for year: 2012 old 2: when to_char(last_day(to_date(&year||'02','yyyymm')),'dd')='29' new 2: when to_char(last_day(to_date(2012||'02','yyyymm')),'dd')='29' IS_LEAP_YEAR --------------------------------- the year you input is a leap year // SQL> / Enter value for year: 1998 old 2: when to_char(last_day(to_date(&year||'02','yyyymm')),'dd')='29' new 2: when to_char(last_day(to_date(1998||'02','yyyymm')),'dd')='29' IS_LEAP_YEAR ------------------------------------- the year you input is not a leap year // 

原帖:http://topic.youkuaiyun.com/u/20110509/13/367c4dea-6aa2-4264-9472-cd9ee7cdc86f.html?seed=1222845748&r=73253860#replyachor