You Asked
I wants to find date difference for given two date in terms of Years, Months and Days. I wrote a function like this
-----------
create or replace function sdate(frmdate date,todate date)
return char is
speldate varchar(30);
tdays number:=0;
tmonth number:=0;
tyear number:=0;
begin
-----------------------------------------------------------------
/*check from_date and to_date are in the same month and year */
-----------------------------------------------------------------
if to_char(frmdate,'MON-YYYY')= to_char(todate,'MON-YYYY') then
select (todate - frmdate)+1 into tdays from dual;
if (to_char(todate,'MM')='02' and
to_char(last_day(todate),'DD')='29') then
if tdays >= 29 then
tdays:=tdays -29;
tmonth:=tmonth+1;
end if;
elsif (to_char(todate,'MM')='02' and
to_char(last_day(todate),'DD')='28') then
if tdays>=28 then
tdays:=tdays -28;
tmonth:=tmonth+1;
end if;
elsif to_char(todate,'MM')
in ('01','03','05','07','08','10','12') then
if tdays >=31 then
tdays := tdays-31;
tmonth := tmonth +1;
end if;
elsif to_char(todate,'MM') in ('04','06','09','11') then
if tdays >=30 then
tdays := tdays-30;
tmonth := tmonth +1;
end if;
end if;
------------------------------------------------
/* check for to_date in the month end or not */
------------------------------------------------
elsif todate = last_day(todate) then
-----------------------------------------------------------------
/* check for if from_date started with first day of month and
to_date end with last day of month */
------------------------------------------------------------------
if to_char(frmdate,'DD')='01' then
select ceil(months_between(todate,frmdate))
into tmonth from dual;
else
select (months_between(last_day(add_months(todate,-1))+1 ,
last_day(frmdate)+1))+1
into tmonth from dual;
select (last_day(frmdate)-frmdate)+1 into tdays from dual;
if (to_char(todate,'MM')='02' and
to_char(last_day(todate),'DD')='29') then
if tdays >= 29 then
tdays:=tdays -29;
tmonth:=tmonth+1;
end if;
elsif (to_char(todate,'MM')='02' and
to_char(last_day(todate),'DD')='28') then
if tdays>=28 then
tdays:=tdays -28;
tmonth:=tmonth+1;
end if;
elsif to_char(todate,'MM') in
('01','03','05','07','08','10','12') then
if tdays >=31 then
tdays := tdays-31;
tmonth := tmonth +1;
end if;
elsif to_char(todate,'MM') in ('04','06','09','11') then
if tdays >=30 then
tdays := tdays-30;
tmonth := tmonth +1;
end if;
end if;
end if;
----------------------------
/*check for from_date */
-----------------------------
else
select (months_between(last_day(add_months(todate,-1))+1 ,
last_day(frmdate)+1))
into tmonth from dual;
select (last_day(frmdate)-frmdate) +
((todate - last_day(add_months(todate,-1))+1)-1) +1
into tdays from dual;
if (to_char(frmdate,'MM')='02' and
to_char(last_day(frmdate),'DD')='29') then
if tdays >= 29 then
tdays:=tdays -29;
tmonth:=tmonth+1;
end if;
elsif (to_char(frmdate,'MM')='02' and
to_char(last_day(frmdate),'DD')='28') then
if tdays>=28 then
tdays:=tdays -28;
tmonth:=tmonth+1;
end if;
elsif to_char(frmdate,'MM') in
('01','03','05','07','08','10','12') then
if tdays >=31 then
tdays := tdays-31;
tmonth := tmonth +1;
end if;
elsif to_char(frmdate,'MM') in ('04','06','09','11') then
if tdays >=30 then
tdays := tdays-30;
tmonth := tmonth +1;
end if;
end if;
end if;
if tmonth >= 12 then
tyear := floor(tmonth/12);
tmonth:= tmonth - (tyear*12);
end if;
select decode(tyear,0,'',tyear || ' Years ') ||
decode(tmonth,0,'',tmonth || ' Months ') ||
decode(tdays,0,'',tdays || ' Days ') into speldate from dual ;
return(speldate);
end;
/
----------
if i execute this function i get a result like this
SQL> select sdate('10-sep-1995','10-sep-1997') from dual;
SDATE('10-SEP-1995','10-SEP-1997')
----------------------------------------------------------------------------------------------------
2 Years 1 Days
------------
If you have any easiest way to find date diff. for given two date in terms of Yrs,Mons and days, Please send it to me.
With Regards
Senthil Kumar. S
and Tom said...
Well, I think there is an easier way -- we can do this in SQL in fact. Consider:
SQL> select older_date,
2 newer_date,
3 years,
4 months,
5 abs(
6 trunc(
7 newer_date-
add_months( older_date,years*12+months )
8 )
9 ) days
10 from ( select
trunc(months_between( newer_date, older_date )/12) YEARS,
11 mod(trunc(months_between( newer_date, older_date )),
12 ) MONTHS,
12 newer_date,
13 older_date
14 from ( select hiredate older_date,
15 add_months(hiredate,rownum)+rownum newer_date
16 from emp )
17 )
18 /
OLDER_DAT NEWER_DAT YEARS MONTHS DAYS
--------- --------- ---------- ---------- ----------
17-DEC-80 18-JAN-81 0 1 1
20-FEB-81 22-APR-81 0 2 2
22-FEB-81 25-MAY-81 0 3 3
02-APR-81 06-AUG-81 0 4 4
28-SEP-81 05-MAR-82 0 5 5
01-MAY-81 07-NOV-81 0 6 6
09-JUN-81 16-JAN-82 0 7 7
09-DEC-82 17-AUG-83 0 8 8
17-NOV-81 26-AUG-82 0 9 9
08-SEP-81 18-JUL-82 0 10 10
12-JAN-83 23-DEC-83 0 11 11
03-DEC-81 15-DEC-82 1 0 12
03-DEC-81 16-JAN-83 1 1 13
23-JAN-82 06-APR-83 1 2 14
14 rows selected.
The inner query:
select hiredate older_date,
add_months(hiredate,rownum)+rownum newer_date
from emp
Is just used to generate "data" to test with. This would normally be "your query" with the two dates in it.
Next, we wrap that query as an inline view to compute the years and months between the two dates. the years between two dates is simply:
trunc(months_between( newer_date, older_date )/12)
we get the number of months between, divide by 12 and truncate the results.
To get the months between is almost as easy:
mod(trunc(months_between( newer_date, older_date )), 12 )
we get the months between as an absolute number and then integer divide (modulo) by 12. The leftover (modulo) piece is the months between without the years.
The DAY component is the "hard" part. What I did was add the YEARS and the MONTHS between to the older date:
add_months(older_date,years*12+months)
subtract that from the newer_date:
newer_date-add_months(older_date,years*12+months)
and then trunc and ABSolute value it. That gives me the days between the two dates.
for your specific dates above -- I get a different answer -- I get:
OLDER_DAT NEWER_DAT YEARS MONTHS DAYS
--------- --------- ---------- ---------- ----------
10-SEP-95 10-SEP-97 2 0 0
which seems to be more accurate.
So, even if you do not use this in SQL -- it would simplify your procedure to:
SQL> create or replace
2 function sdate( p_date1 in date, p_date2 in date )
return varchar2
3 as
4 older_date date default least( p_date1, p_date2 );
5 newer_date date default greatest( p_date1, p_date2 );
6
7 years number default
8 trunc(months_between( newer_date, older_date )/12);
9 months number default
10 mod(trunc(months_between(newer_date,older_date)),12);
11 days number default
12 abs(trunc(newer_date-
add_months(older_date,years*12+months)));
13
14 return_string varchar2(255);
15 begin
16 if (Years > 0 ) then
return_string := return_string || years || ' years ';
17 end if;
18 if (Months> 0 ) then
return_string := return_string || months|| ' months ';
19 end if;
20 if (Days > 0 ) then
return_string := return_string || days || ' days ';
21 end if;
22
23 return rtrim(return_string);
24 end;
25 /
Function created.
SQL> column sdate format a25
SQL> select sdate('10-sep-1995','10-sep-1997') sdate from dual;
SDATE
-------------------------
2 years
SQL>
SQL> select sdate( newer_date, older_date )
2 from ( select hiredate older_date,
3 add_months(hiredate,rownum)+rownum newer_date
4 from emp )
5 /
SDATE(NEWER_DATE,OLDER_DATE)
-----------------------------------------------------------------------------------------
1 months 1 days
2 months 2 days
3 months 3 days
4 months 4 days
5 months 5 days
6 months 6 days
7 months 7 days
8 months 8 days
9 months 9 days
10 months 10 days
11 months 11 days
1 years 12 days
1 years 1 months 13 days
1 years 2 months 14 days
14 rows selected.
SQL>
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment