OraFAQ Forum: SQL & PL/SQL » Need to convert Number of Days in to Years Month Days format

Home » SQL & PL/SQL » SQL & PL/SQL » Need to convert Number of Days in to Years Month Days format

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Need to convert Number of Days in to Years Month Days format [message #138808]

Fri, 23 September 2005 22:27

gi_srinivas
Messages: 19
Registered: September 2005
Location: Hyderan

Junior Member

Hi,
I Want to convert the number of days to Year month and days.

ACtually this is my code

V_diff :=fffunc.DAYS_BETWEEN('01-OCT-2005','19-SEP-2004');

Now in one of the variables its assigning number of days.
a:= pay_balance_pkg.get_value(v_defined_balance_id,r_Emp_Det.Assignment_id,'01-OCT-2005');

Now a contains number data.
For Ex: 10. that means 10 days.

Now in my calculation of Loss of service (v_los) in my procedure

v_los := V_diff - a;
i.e., v_los := 377-10 that is v_los := 367

Now i want to display this value in the form of 1 year 0 month 1 day
like that.
that is in the format of years months days.

Please can anybody help me on this requirement.

Thanks and regards,
Srinivas G

Report message to a moderator

Re: Need to convert Number of Days in to Years Month Days format [message #138818 is a reply to message #138808]

Sat, 24 September 2005 01:35

Frank
Messages: 7901
Registered: March 2000

Senior Member

How many days are there in a year? And in a month?
These figures are not fixed, so you will have to define them. After you do so (e.g. a month is 30 days, a year is 365 days) all that is left is simple arithmetic. (using mod)

hth

Report message to a moderator

Re: Need to convert Number of Days in to Years Month Days format [message #138819 is a reply to message #138818]

Sat, 24 September 2005 01:55

gi_srinivas
Messages: 19
Registered: September 2005
Location: Hyderan

Junior Member

Hi Frank,
Thanks for the reply.
But Im not able to find out the exact logic.
can you please help me.

Srinivas

Report message to a moderator

Re: Need to convert Number of Days in to Years Month Days format [message #138836 is a reply to message #138808]

Sat, 24 September 2005 05:34

rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India

Senior Member

Hi,

Frank wrote :

Quote:

How many days are there in a year? And in a month?

I don't think that,calculating days in between by taking care of such facts (a month is 30 days, a year is 365 days) will not be appropriate method as there can be 366 day in year,28/31 days a month.

Instead you can apply some day functions like ADD_MONTHS,MONTHS_BETWEEN as :

SQL> select YEARS,trunc(MB-YEARS*12) MONTHS,ND-add_months(OD,trunc(MB)) DAYS
  2  from
  3  (select OD,ND,months_between(ND,OD) MB,trunc(months_between(ND,OD)/12) YEARS from
  4  (select sysdate nd,sysdate-365 od from dual));

     YEARS     MONTHS       DAYS
---------- ---------- ----------
         1          0          0

SQL> select YEARS,trunc(MB-YEARS*12) MONTHS,ND-add_months(OD,trunc(MB)) DAYS
  2  from
  3  (select OD,ND,months_between(ND,OD) MB,trunc(months_between(ND,OD)/12) YEARS from
  4  (select sysdate nd,sysdate-366 od from dual));

     YEARS     MONTHS       DAYS
---------- ---------- ----------
         1          0          1

SQL> select YEARS,trunc(MB-YEARS*12) MONTHS,ND-add_months(OD,trunc(MB)) DAYS
  2  from
  3  (select OD,ND,months_between(ND,OD) MB,trunc(months_between(ND,OD)/12) YEARS from
  4  (select sysdate nd,sysdate-395 od from dual));

     YEARS     MONTHS       DAYS
---------- ---------- ----------
         1          0         30

SQL> select YEARS,trunc(MB-YEARS*12) MONTHS,ND-add_months(OD,trunc(MB)) DAYS
  2  from
  3  (select OD,ND,months_between(ND,OD) MB,trunc(months_between(ND,OD)/12) YEARS from
  4  (select sysdate nd,sysdate-396 od from dual));

     YEARS     MONTHS       DAYS
---------- ---------- ----------
         1          1          0

SQL> select YEARS,trunc(MB-YEARS*12) MONTHS,ND-add_months(OD,trunc(MB)) DAYS
  2  from
  3  (select OD,ND,months_between(ND,OD) MB,trunc(months_between(ND,OD)/12) YEARS from
  4  (select sysdate nd,sysdate-397 od from dual));

     YEARS     MONTHS       DAYS
---------- ---------- ----------
         1          1          1
SQL>

Rajuvan.

[Updated on: Sat, 24 September 2005 06:15]

Report message to a moderator

Re: Need to convert Number of Days in to Years Month Days format [message #138842 is a reply to message #138836]

Sat, 24 September 2005 06:37

gi_srinivas
Messages: 19
Registered: September 2005
Location: Hyderan

Junior Member

Hi Raju,
Thanq so much. Its perfectly working fine as per the requirement.

Thanks once again
Srinivas.

Report message to a moderator

Previous Topic:	comparision for range of values using substr
Next Topic:	Tranfer Data From Access into Oracle

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jun 25 01:59:33 CDT 2026