Hi ALl,

IS there any way in PL/SQL to identify an year as a Leap Year?

Thanks,
Satish

Satish wrote:

> Hi ALl,
>
> IS there any way in PL/SQL to identify an year as a Leap Year?
>
> Thanks,
> Satish

CREATE OR REPLACE FUNCTION is_leap_year (yearstr VARCHAR2)
RETURN BOOLEAN IS

d DATE;

BEGIN
d := TO_DATE('29-FEB-' || yearstr);
RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
RETURN FALSE;
END;
/

Daniel Morgan

ats wrote:
> Satish wrote:

> > IS there any way in PL/SQL to identify an year as a Leap Year?

>
> CREATE OR REPLACE FUNCTION is_leap_year (yearstr VARCHAR2)
> RETURN BOOLEAN IS
>
> d DATE;
>
> BEGIN
> d := TO_DATE('29-FEB-' || yearstr);
> RETURN TRUE;
> EXCEPTION
> WHEN OTHERS THEN
> RETURN FALSE;
> END;
> /


I'd write it as d := TO_DATE('29-FEB-' || yearstr, 'dd=MON-yyyy');

That way it won't break if someone changes the default date format.


--
//-Walt
//
//

Walt wrote:

> ats wrote:
>
>>Satish wrote:
>
>
>>>IS there any way in PL/SQL to identify an year as a Leap Year?
>
>
>>CREATE OR REPLACE FUNCTION is_leap_year (yearstr VARCHAR2)
>>RETURN BOOLEAN IS
>>
>>d DATE;
>>
>>BEGIN
>> d := TO_DATE('29-FEB-' || yearstr);
>> RETURN TRUE;
>>EXCEPTION
>> WHEN OTHERS THEN
>> RETURN FALSE;
>>END;
>>/
>
>
>
> I'd write it as d := TO_DATE('29-FEB-' || yearstr, 'dd=MON-yyyy');
>
> That way it won't break if someone changes the default date format.

Good observation. Thanks.

--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

"ats" <damorgan@x.washington.edu> wrote in message
news:1097552599.809495@yasure...
....
| Daniel A. Morgan
| University of Washington
| damorgan@x.washington.edu
| (replace 'x' with 'u' to respond)

what's with the new moniker?

++ mcs

ats wrote:
>
> Walt wrote:
>
> > ats wrote:
> >
> >>Satish wrote:
> >
> >
> >>>IS there any way in PL/SQL to identify an year as a Leap Year?
> >
> >
> >>CREATE OR REPLACE FUNCTION is_leap_year (yearstr VARCHAR2)
> >>RETURN BOOLEAN IS
> >>
> >>d DATE;
> >>
> >>BEGIN
> >> d := TO_DATE('29-FEB-' || yearstr);
> >> RETURN TRUE;
> >>EXCEPTION
> >> WHEN OTHERS THEN
> >> RETURN FALSE;
> >>END;
> >>/
> >
> >
> >
> > I'd write it as d := TO_DATE('29-FEB-' || yearstr, 'dd=MON-yyyy');
> >
> > That way it won't break if someone changes the default date format.
>
> Good observation. Thanks.
>
> --
> Daniel A. Morgan
> University of Washington
> damorgan@x.washington.edu
> (replace 'x' with 'u' to respond)

I can't remember if my algorithm is correct, but avoiding the data type
conversion might be more efficient...

SQL> create or replace
2 function f1(y number) return boolean is
3 x date;
4 begin
5 x := to_date('2902'||y,'ddmmyyyy');
6 return true;
7 exception
8 when others then return false;
9 end;
10 /

Function created.

SQL> create or replace
2 function f2(y number) return boolean is
3 begin
4 return mod(y,4)=0 and ( mod(y,100) != 0 or mod(y,400) = 0 );
5 end;
6 /

Function created.

SQL> set timing on
SQL> declare
2 l boolean;
3 begin
4 for i in 1 .. 500000 loop
5 l := f1(1234);
6 end loop;
7 end;
8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.96
SQL> declare
2 l boolean;
3 begin
4 for i in 1 .. 500000 loop
5 l := f2(1234);
6 end loop;
7 end;
8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.09

Cheers
Connor
--
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
ISBN: 1590592174

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald@yahoo.com

Coming Soon! "Oracle Insight - Tales of the OakTable"

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"

------------------------------------------------------------

Mark C. Stock wrote:

> "ats" <damorgan@x.washington.edu> wrote in message
> news:1097552599.809495@yasure...
> ...
> | Daniel A. Morgan
> | University of Washington
> | damorgan@x.washington.edu
> | (replace 'x' with 'u' to respond)
>
> what's with the new moniker?
>
> ++ mcs

Upgraded from Netscape 7.1 to 7.2 and setting it up I put the wrong
info into the account name.

As I've said several times ... I have a consulting company. I keep it
well hidden so as not to be a spammer and because I have so much
business I don't need any more. Unfortunately the initials
for the corporate account name got where the my name should have been.

Hopefully it is corrected now.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

"Daniel Morgan" <damorgan@x.washington.edu> wrote in message
news:1097720689.582325@yasure...
| Mark C. Stock wrote:
|
| > "ats" <damorgan@x.washington.edu> wrote in message
| > news:1097552599.809495@yasure...
| > ...
| > | Daniel A. Morgan
| > | University of Washington
| > | damorgan@x.washington.edu
| > | (replace 'x' with 'u' to respond)
| >
| > what's with the new moniker?
| >
| > ++ mcs
|
| Upgraded from Netscape 7.1 to 7.2 and setting it up I put the wrong
| info into the account name.
|
| As I've said several times ... I have a consulting company. I keep it
| well hidden so as not to be a spammer and because I have so much
| business I don't need any more. Unfortunately the initials
| for the corporate account name got where the my name should have been.
|
| Hopefully it is corrected now.
| --
| Daniel A. Morgan
| University of Washington
| damorgan@x.washington.edu
| (replace 'x' with 'u' to respond)
|

all's back to normal, i can delete my replace(poster,'ats','daniel morgan')
synapses now!

++ mcs