how to check leap-year
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: how to check leap-year

  1. #1
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639

    how to check leap-year

    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.....

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Code:
    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;
    /
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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).

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,478

    Cool

    Another option:
    Code:
    select  'Leap Year' from dual
     where 28 < to_char(last_day(to_date('20040201','YYYYMMDD')),'DD')
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  5. #5
    Join Date
    Apr 2002
    Location
    Phoenix, AZ
    Posts
    175

    Lightbulb

    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.
    Sridhar R Patnam

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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:
    Code:
    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.

  7. #7
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    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

  8. #8
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    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 ;

  9. #9
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    Hi....sorry..i found my error..thanks

  10. #10
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    Hi....sorry..i found my error..thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width