Oracle Year/Month validation!

Function can not return you all values (except if you need them as a long string). If you need them as a numbers procedure is the correct choice.

why is this being performed in Oracle?
Is it a "front-end" need? (in the User Interface) If so the front-end can collect the 2 inputs and present the result, e.g. javascript could do this

Thank you all for the valuable comment.

The requirement is something like this

Ex;;
--
Looking for
Create or replace function create_date ( p_date in varchar2)  Ex --  29-feb-1992
return varchar2
is
v_year number;
v_month number;
v_days number;
v_hrs number;
begin
         
          .....1st looking for validation a logic the code should check the enter date is leap year/not  if not then just null it

          .....2nd looking for NO of  " Year: Month : Days : Hour"  
         
..In return type expecting something like following

return  v_year||' Year : '||v_month||' Month : '||v_days ||' Days'||v_hrs ||'  hrs' || 'Is a Leap year';

end;
/

Please let me know if there any concern

I appreciate if you or anybody, help me out in achieving this task. I
urgently need to apply this function in one of my D3 report.

create or replace  function calculate_date (p_date in date ) return VARCHAR2
as
v_yr number := substr(to_char(P_DATE,'yyyy'), 1,4);
v_leap varchar2(15);
v_non_leap varchar2(1):=null;
v_year varchar2(2);
v_month varchar2(5);
v_days varchar2(5);
begin
--Checking leap-year
--Here the Leap year logic is working well
if mod(v_yr,4)=0 then
        if mod(v_yr,100) <> 0 then
        v_leap := 'Is a Leap Yr';
        dbms_output.put_line(v_leap) ;
        else
            if mod (v_yr,400)=0 then
            v_leap := 'Is a Leap Yr';
               dbms_output.put_line(v_leap);
            else                  
               dbms_output.put_line(v_non_leap);
            end if;
        end if;
end if;

--I'm little confuse how to do following code to working as dynamic could anybody please guide me
--how can i pass the parameter into following hard coded code in my case "'29-feb-1992'"

select trunc(months_between(sysdate,'29-feb-1992')/12),
      MOD (TRUNC (MONTHS_BETWEEN (sysdate,'29-feb-1992')), 12),
      TRUNC(sysdate - ADD_MONTHS ('29-feb-1992', TRUNC(MONTHS_BETWEEN (sysdate,'29-feb-1992')))) into v_year, v_month, v_days
      from dual;

--v_year := trunc(months_between(sysdate,'p_date')/12);
--v_month := MOD (TRUNC (MONTHS_BETWEEN (sysdate,'p_date')), 12);
--v_days := TRUNC ( sysdate - ADD_MONTHS ('p_date', TRUNC(MONTHS_BETWEEN (sysdate,'p_date'))));

return v_year||' Year : '||v_month||' Month : '||v_days ||' Days '||v_leap;
end;


SQL>select calculate_date('01-feb-1992') from dual;
      26 Year : 4 Month : 24 Days Is a Leap Yr
SQL>select calculate_date('01-feb-1993') from dual;
      26 Year : 4 Month : 24 Days

Fyi @johnsone


create or replace
function calculate_date (p_date in varchar2 )  -- varchar2 instead of date
return VARCHAR2
as
v_yr number := substr(to_char(P_DATE,'yyyy'), 1,4);
v_leap varchar2(15);
v_non_leap varchar2(1):=null;
v_year varchar2(2);
v_month varchar2(2);
v_days varchar2(3);
begin
--Checking leap-year
if mod(v_yr,4)=0 then
        if mod(v_yr,100) <> 0 then
        v_leap := 'Is a Leap Yr';
        dbms_output.put_line(v_leap) ;
        else
               if mod (v_yr,400)=0 then
             v_leap := 'Is a Leap Yr';
                    dbms_output.put_line(v_leap);
               else
               dbms_output.put_line(v_non_leap);
               end if;
        end if;
end if;

--Testing with Hard coded date value
select trunc((months_between(sysdate,'29-feb-1992')/12)),
      MOD (TRUNC (MONTHS_BETWEEN (sysdate,'29-feb-1992')), 12),
      TRUNC(sysdate - ADD_MONTHS ('29-feb-1992', TRUNC(MONTHS_BETWEEN (sysdate,'29-feb-1992')))) into v_year, v_month, v_days
      from dual;


return v_year||' Year : '||v_month||' Month : '||v_days ||' Days '||v_leap;
end;


I have tried to replace the parameter datatype with varchar2 it's getting error

SQL>select calculate_date('01-Feb-1992') from dual;
       ORA-06502: PL/SQL: numeric or value error: character to number conversion error
       ORA-06512: at "USER1.CALCULATE_DATE", line 5
       06502. 00000 -  "PL/SQL: numeric or value error%s"

Thank you once again @johnsone for the comment

Are you want me to convert it to like to_date(p_date,'MM/DD/YYYY') ? In following code? in this case::  '29-feb-1992'

Please mention the point where i have to convert it to date and share example!

select trunc((months_between(sysdate,'29-feb-1992')/12)),
      MOD (TRUNC (MONTHS_BETWEEN (sysdate,'29-feb-1992')), 12),
      TRUNC(sysdate - ADD_MONTHS ('29-feb-1992', TRUNC(MONTHS_BETWEEN (sysdate,'29-feb-1992')))) into v_year, v_month, v_days
      from dual;

Looks Good Thanks @awking.

I have customize it as i'm looking for both information (Leap/Non-Leap Yr).

create or replace
function create_date(p_date in varchar2)
return varchar2 is
v_date date;
v_leap varchar2(15);
v_non_leap varchar2(100);
v_year number;
v_yrs number;
v_mths number;
v_days number;
v_hrs number;
v_mins number;

begin
v_date := to_date(p_date,'DD-MON-YYYY');
v_year := extract(year from v_date);
if mod(v_year,4)=0 then
        if mod(v_year,100) <> 0 then
        v_leap := 'Is a Leap Yr';
        elsif mod (v_year,400)=0 then
             v_leap := 'Is a Leap Yr';
               else
                v_non_leap := null;
        end if;
end if;

if v_leap is not null then
  v_yrs := floor(months_between(sysdate,to_date(p_date,'DD-MON-YYYY'))/12);
  v_mths := floor(months_between(sysdate,to_date(p_date,'DD-MON-YYYY')) - v_yrs*12);
  v_days := floor(sysdate - add_months(v_date,v_yrs*12 + v_mths));
  v_hrs := floor(mod(sysdate - add_months(v_date,v_yrs*12 + v_mths),1) * 24);
  v_mins := round(mod(mod(sysdate - add_months(v_date,v_yrs*12 + v_mths),1) * 24,v_hrs) * 60,0);
elsif v_non_leap is null then
  v_yrs := floor(months_between(sysdate,to_date(p_date,'DD-MON-YYYY'))/12);
  v_mths := floor(months_between(sysdate,to_date(p_date,'DD-MON-YYYY')) - v_yrs*12);
  v_days := floor(sysdate - add_months(v_date,v_yrs*12 + v_mths));
  v_hrs := floor(mod(sysdate - add_months(v_date,v_yrs*12 + v_mths),1) * 24);
  v_mins := round(mod(mod(sysdate - add_months(v_date,v_yrs*12 + v_mths),1) * 24,v_hrs) * 60,0);
end if;

if v_leap is not null then
  return v_yrs||' years, '||v_mths||' months, '||v_days||' days, '||v_hrs||' hours, and '||v_mins||' minutes'||' '||v_leap;
elsif v_non_leap is null then
  return v_yrs||' years, '||v_mths||' months, '||v_days||' days, '||v_hrs||' hours, and '||v_mins||' minutes';
end if;
end;

SQL> select create_date('1/feb/1992') from dual;
26 years, 5 months, 24 days, 14 hours, and 4 minutes Is a Leap Yr
SQL>select create_date('1/feb/1997') from dual;
21 years, 5 months, 24 days, 14 hours, and 5 minutes

Thank You Once Again!

FYi@slightwv

I have removed the v_non_leap here and handle it with v_leap only, i'm little scared of using the following  code inside it?

https://www.experts-exchange.com/questions/29110457/ORA-06553-PLS-382-expression-is-of-wrong-type.html?anchorAnswerId=42632436#a42632436

Please comment me where i can make the block more efficient using the above your code?


create or replace
function create_date(p_date in varchar2)
return varchar2 is
v_date date;
v_leap varchar2(15);
--v_non_leap varchar2(100);
v_year number;
v_yrs number;
v_mths number;
v_days number;
v_hrs number;
v_mins number;

begin
v_date := to_date(p_date,'DD-MON-YYYY');
v_year := extract(year from v_date);
if mod(v_year,4)=0 then
        if mod(v_year,100) <> 0 then
        v_leap := 'Is a Leap Yr';
        elsif mod (v_year,400)=0 then
             v_leap := 'Is a Leap Yr';
               else
                v_leap := null;
        end if;
end if;

if v_leap is not null then
  v_yrs := floor(months_between(sysdate,to_date(p_date,'DD-MON-YYYY'))/12);
  v_mths := floor(months_between(sysdate,to_date(p_date,'DD-MON-YYYY')) - v_yrs*12);
  v_days := floor(sysdate - add_months(v_date,v_yrs*12 + v_mths));
  v_hrs := floor(mod(sysdate - add_months(v_date,v_yrs*12 + v_mths),1) * 24);
  v_mins := round(mod(mod(sysdate - add_months(v_date,v_yrs*12 + v_mths),1) * 24,v_hrs) * 60,0);
else
  v_yrs := floor(months_between(sysdate,to_date(p_date,'DD-MON-YYYY'))/12);
  v_mths := floor(months_between(sysdate,to_date(p_date,'DD-MON-YYYY')) - v_yrs*12);
  v_days := floor(sysdate - add_months(v_date,v_yrs*12 + v_mths));
  v_hrs := floor(mod(sysdate - add_months(v_date,v_yrs*12 + v_mths),1) * 24);
  v_mins := round(mod(mod(sysdate - add_months(v_date,v_yrs*12 + v_mths),1) * 24,v_hrs) * 60,0);
end if;

if v_leap is not null then
  return v_yrs||' years, '||v_mths||' months, '||v_days||' days, '||v_hrs||' hours, and '||v_mins||' minutes'||' '||v_leap;
--elsif v_non_leap is null then
else
  return v_yrs||' years, '||v_mths||' months, '||v_days||' days, '||v_hrs||' hours, and '||v_mins||' minutes';
end if;

end;

I agree with slightwv.  Don't try to write this PL\SQL function yourself!  This problem has been solved in the past by other experienced PL\SQL programmers like Tom Kyte.  There are very few people in the world who write Pl\SQL code as good as his code.  (Steve Feuerstein is close.)

So start with the function from Tom Kyte that slightwv copied here.  Or, if you don't like that one for some reason, other people have posted other similar solutions for this problem on this site in the past.  You could search for one of those.  Then just add a check for leap year to that,

Small clarification:  It wasn't Tom's code.  Just posted on his site.  He didn't trash it so I assume it is good.

There is another possible problem to your defined validation:
You pass in a DD/MON/YY with no time portion.  That defaults to midnight.  sysdate is down to the second it is executed.

Not sure what the hours, minutes and seconds represent in your requirements but it will be from the time the function is called to midnight on the date passed in.

Please use code blocks, just click "CODE" in the toolbar above any comment edit area, then paste code between the tags. For example:

create or replace 
function create_date(p_date in varchar2)
return varchar2 is
v_date date;
v_leap varchar2(15);
--v_non_leap varchar2(100);
v_year number;
v_yrs number;
v_mths number;
v_days number;
v_hrs number;
v_mins number;

begin
v_date := to_date(p_date,'DD-MON-YYYY');
v_year := extract(year from v_date);
if mod(v_year,4)=0 then
        if mod(v_year,100) <> 0 then
        v_leap := 'Is a Leap Yr';
        elsif mod (v_year,400)=0 then
             v_leap := 'Is a Leap Yr';
               else
                v_leap := null;
        end if;
end if;

if v_leap is not null then
  v_yrs := floor(months_between(sysdate,to_date(p_date,'DD-MON-YYYY'))/12);
  v_mths := floor(months_between(sysdate,to_date(p_date,'DD-MON-YYYY')) - v_yrs*12);
  v_days := floor(sysdate - add_months(v_date,v_yrs*12 + v_mths));
  v_hrs := floor(mod(sysdate - add_months(v_date,v_yrs*12 + v_mths),1) * 24);
  v_mins := round(mod(mod(sysdate - add_months(v_date,v_yrs*12 + v_mths),1) * 24,v_hrs) * 60,0);
else
  v_yrs := floor(months_between(sysdate,to_date(p_date,'DD-MON-YYYY'))/12);
  v_mths := floor(months_between(sysdate,to_date(p_date,'DD-MON-YYYY')) - v_yrs*12);
  v_days := floor(sysdate - add_months(v_date,v_yrs*12 + v_mths));
  v_hrs := floor(mod(sysdate - add_months(v_date,v_yrs*12 + v_mths),1) * 24);
  v_mins := round(mod(mod(sysdate - add_months(v_date,v_yrs*12 + v_mths),1) * 24,v_hrs) * 60,0);
end if;

if v_leap is not null then
  return v_yrs||' years, '||v_mths||' months, '||v_days||' days, '||v_hrs||' hours, and '||v_mins||' minutes'||' '||v_leap;
--elsif v_non_leap is null then
else
  return v_yrs||' years, '||v_mths||' months, '||v_days||' days, '||v_hrs||' hours, and '||v_mins||' minutes';
end if;

end;

Select allOpen in new window

and you can refer to line numbers in subsequent discussion.