CREATE OR REPLACE FUNCTION HR.is_leap_year ( date_in IN DATE)
RETURN NUMBER
AS
result NUMBER := 0;
BEGIN
SELECT
CASE WHEN (
MOD(EXTRACT(YEAR FROM date_in), 4) = 0 AND MOD(EXTRACT(YEAR FROM date_in) ,100) != 0)
OR MOD(date_in, 400) = 0 THEN 1 ELSE 0 END AS bit
INTO result
FROM DUAL;
RETURN result;
END is_leap_year ;
/
CREATE OR REPLACE FUNCTION HR.is_leap_year ( date_in IN DATE)
RETURN NUMBER
AS
result NUMBER := 0;
BEGIN
result := CASE WHEN
( MOD(
EXTRACT(YEAR FROM date_in),
4) = 0
AND
MOD(
EXTRACT(YEAR FROM date_in) ,
100) != 0
)
OR
MOD(date_in, 400) = 0 THEN 1 ELSE 0 END;
RETURN result;
END is_leap_year ;
/
CREATE OR REPLACE FUNCTION HR.is_leap_year (date_in IN DATE)
RETURN NUMBER AS
result NUMBER := 0;
BEGIN
SELECT CASE
WHEN (MOD (EXTRACT (YEAR FROM date_in), 4) = 0
AND MOD (EXTRACT (YEAR FROM date_in), 100) != 0)
OR MOD (EXTRACT (YEAR FROM date_in), 400) = 0 THEN ---- This is corrected.
1
ELSE
0
END
AS bit
INTO result
FROM DUAL;
RETURN result;
END is_leap_year;
/
Cheers,ricard888 wrote:SQL> SHOW ERROR
Hi Jeenesh, i would if i got errror message but all i got is "Warning: compiled but with compilation errors"
and that is why i didn't posted
ricard888 wrote:I doubt client version matters.
Warning: compiled but with compilation errors
No errors.
i am scratching my head.... i am currently using oracle 9 client running on 11g. is that a reason
OR MOD( EXTRACT(YEAR FROM date_in) , 400) = 0 THEN 1 ELSE 0 END AS bit
-- OR MOD( date_in , 400) = 0 THEN 1 ELSE 0 END AS bit -- at line 10
Regards Manik wrote:Why use the SQL engine to do the calculation, when your code is inside the PL/SQL engine that is perfectly capable of doing the calculation?CREATE OR REPLACE FUNCTION HR.is_leap_year (date_in IN DATE) RETURN NUMBER AS result NUMBER := 0; BEGIN SELECT CASE WHEN (MOD (EXTRACT (YEAR FROM date_in), 4) = 0 AND MOD (EXTRACT (YEAR FROM date_in), 100) != 0) OR MOD (EXTRACT (YEAR FROM date_in), 400) = 0 THEN ---- This is corrected. 1 ELSE 0 END AS bit INTO result FROM DUAL; RETURN result; END is_leap_year; /
ricard888 wrote:Do not use SQL when SQL is not needed - context switching from the PL/SQL engine to the SQL engine can be expensive.
can someone please assist
SQL> create or replace function isLeapYear( d date ) return integer is
2 begin
3 return(
4 case
5 when ( mod(extract(year from d),4) = 0 and
6 mod(extract(year from d),100) != 0
7 )
8 or
9 mod(extract(year from d),400) = 0 then
10 1
11 else
12 0
13 end
14 );
15 end;
16 /
Function created.
SQL>
SQL> select isLeapYear(sysdate) as BIT from dual;
BIT
----------
1
SQL> select isLeapYear(sysdate+300) as BIT from dual;
BIT
----------
0
SQL>
i suppose next step take logic from the function and combined for the requirement. for example below.
(CASE WHEN Month (“Table Date”.”Date”) IN (1, 3, 5, 7, 8, 10, 12) THEN 31
WHEN Month (“Table Date”.”Date”) IN (4, 6, 9, 11) THEN 30
ELSE (CASE WHEN (MOD (YEAR (“Table Date”.”Date”), 4) = 0 AND MOD (YEAR (“Table Date”.”Date”), 100)! = 0) OR (MOD (YEAR (“Table Date”.”Date”), 400) = 0) THEN 29
ricard888 wrote:Are you trying to get the last day of a month? If so, use the LAST_DAY() function.i suppose next step take logic from the function and combined for the requirement. for example below. (CASE WHEN Month (“Table Date”.”Date”) IN (1, 3, 5, 7, 8, 10, 12) THEN 31 WHEN Month (“Table Date”.”Date”) IN (4, 6, 9, 11) THEN 30 ELSE (CASE WHEN (MOD (YEAR (“Table Date”.”Date”), 4) = 0 AND MOD (YEAR (“Table Date”.”Date”), 100)! = 0) OR (MOD (YEAR (“Table Date”.”Date”), 400) = 0) THEN 29
create or replace function IS_LEAP_YEAR (nYr in number) return boolean is
v_day varchar2(2);
begin
select to_char(last_day(to_date( '01-FEB-'|| to_char(nYr), 'DD-MON-YYYY')), 'DD') into v_day from dual;
if v_day = '29' then -- if v_day = 29 then it must be a leap year, return TRUE
return TRUE;
else
return FALSE; -- otherwise year is not a leap year, return false
end if;
end;
This is taken from [url http://www.dba-oracle.com/t_detect_leap_year_function.htm] Burleson Consulting.