how to check leap-year

[kris123]

Hi,

Im making a statistical report (by year/month/day) using sqlplus. I have to accept the desired year and month. How can i check if its a leap year? Coz i need to print only(feb 1-28) daily count report if its not a leap year.


Thanks in advance.....

[jmodic]

create or replace function is_leap_year
 (p_year in number)
return varchar2
is
  l_dummy date;
begin
  select to_date('29.02.' || p_year, 'dd.mm.yyyy')
    into l_dummy
    from dual;
  return 'TRUE';
exception
  when others then return 'FALSE';
end;
/

[DaPi]

Just for fun, go here: http://www.tondering.dk/claus/calendar.html

You will find out that, in Sweden, February had 30 days in 1712! (Not a leap year, more a hop-step-and-jump year).

The site includes a useful formula for calculating the date of Easter according to the Roman/Western tradition (as opposed to orthodox).

[LKBrwn_DBA]

Another option:

select  'Leap Year' from dual
 where 28 < to_char(last_day(to_date('20040201','YYYYMMDD')),'DD')

[patnams]

Another interesting thing is in year 1752. A about 12 days ares in September. This is becasue they changed from Gregorian(?) to Julian(?) formats .

Oracle does not take care of this.

On unix do "cal 1752" and look for September, from 2 it jumps to 14.

[DaPi]

Originally posted by patnams
Another interesting thing is in year 1752. A about 12 days ares in September. This is becasue they changed from Gregorian(?) to Julian(?) formats .

Oracle does not take care of this.

On unix do "cal 1752" and look for September, from 2 it jumps to 14.

It's all in the link I gave above: http://www.tondering.dk/claus/cal/no...00000000000000

Note that different parts of the USA changed at VERY different times - Oracle apparently went over in 1582, whereas UNIX was a little late:

declare
ans varchar2(50);
begin
for i IN 1..20 loop
select to_char(to_date('01-OCT-1582','DD-MON-YYYY')+i,'DD-MON-YYYY') into ans from dual;
dbms_output.put_line(ans);
end loop;
end;
/

Bill Gates gets leap years wrong - type 60 into an Excel sheet and format it as Date.

[kris123]

Hi friends,

This is the sample pl/sql program that i made...but i get ORA-06550
although the update code runs on sqlplus.
Can u help me some work-around pls....
Thanks a lot.

--------
set term on
set echo off
set verify off
set serveroutput on size 100000
set feedback off
set heading off
set lines 75
spool apec_stat_rpt.txt

ACCEPT month1 CHAR prompt 'Please enter Report Month : '
accept year1 char prompt 'Please enter Report Year : '

declare

ctr number(2);

begin

select to_char(last_day(to_date(&month1||&year1,'MONYYYY')),'DD') into ctr;

for i IN 0..ctr loop

rem update apec_stat_rpt set bid_clr=(select nvl(count(*),0) from apec
where to_date(substr(BID_REPLY,1,11),'dd/Mon/yyyy')=to_date('01/&month1/&year1','dd/MON/yyyy')+i)
where date1=i+1 and bid_permission='YES';

end loop;
/

update apec_stat_rpt set bid_clr=(select nvl(count(*),0) from apec where bid_permission='YES'
*
ERROR at line 11:
ORA-06550: line 7, column 69:
PL/SQL: ORA-00923: FROM keyword not found where expected
ORA-06550: line 7, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 15, column 9:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the
following:
begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with

[kris123]

sorry...i already removed the "rem"

update apec_stat_rpt set bid_clr=(select nvl(count(*),0) from apec where bid_permission='YES'
and to_date(substr(BID_REPLY,1,11),'dd/Mon/yyyy')=to_date('01/&month1/&year1','dd/MON/yyyy')+i)
where date1=i+1 ;

[kris123]

Hi....sorry..i found my error..thanks

[kris123]

Hi....sorry..i found my error..thanks