This discussion is archived
10 Replies Latest reply: Sep 30, 2013 12:41 PM by 1002933

# Kindly suggest how to convert number into days,months,years

Currently Being Moderated

Dear All,

I have a requirement, where I get a value from function, say 61  (in days).

I want to show in the report it as,  (60/30 + 1 )

Years  Months  Days

0          2            1

Kindly suggest.

Regards,

Afzal.

• ###### 1. Re: Kindly suggest how to convert number into days,months,years
Currently Being Moderated

Not easy to answer since months have different number of days.

• ###### 2. Re: Kindly suggest how to convert number into days,months,years
Currently Being Moderated

What are you trying to solve?

• ###### 3. Re: Kindly suggest how to convert number into days,months,years
Currently Being Moderated

I am calculating total number of days for the employee having leave as 'unpaid leave' through a function xx_fun_1.

This function gives me output as 61 days.

I need to display in my oracle rdf report as , 0  years,2 months and 1 day.

Years    Months    Days

0               2             1

Regards,

Afzal.

• ###### 4. Re: Kindly suggest how to convert number into days,months,years
Currently Being Moderated

If you can live with 30 days months (and 360 days years) - certain banks do

select :days input,

trunc(:days / 360) years,

trunc(mod(:days,360) / 30) months,

mod(:days,30) days

from dual

Regards

Etbin

• ###### 5. Re: Kindly suggest how to convert number into days,months,years
Currently Being Moderated

Hi,

No need to mention the different months, we just need to divide with 30, to get months as I mentioned in my question.

61/30

we need to get (2 months + 1 day) irrespective of month.

• ###### 6. Re: Kindly suggest how to convert number into days,months,years
Currently Being Moderated

'unpaid leave'  sure has a known start date and end date.

Your report would be more accurate starting with those.

Regards

Etbin

• ###### 7. Re: Kindly suggest how to convert number into days,months,years
Currently Being Moderated

As others already pointed out - different months have different number of days. So in general it is not possible to tell how many months 61 days is. In your case you need to use combination of unpaid leave start date and number of days. So, for example if unpaid leave started February 1, 2013 and lasted 61 days, then it would be 2 months and 2 days. And if it started July 1, 2013 then it would be 1 month and 30 days.

SY.

• ###### 8. Re: Kindly suggest how to convert number into days,months,years
Currently Being Moderated

Thanks Etbin and Solomon for the reply.

It is sum of all the unpaid leaves taken by the employee in his service, So we need to sum all the total days as 61 days and then I need to show it as months,days as described above.

Let me test tomorrow in office the query sent by Etbin and will update you all here...

Thanks a lot once again to all of you .................... : )

Regards,

Afzal.

• ###### 9. Re: Kindly suggest how to convert number into days,months,years
Currently Being Moderated

You could act as a some kind of astronomer: Year - Wikipedia, the free encyclopedia

select :days input,

floor(:days / 365.25) years,

floor(mod(:days,365.25) / (365.25 / 12)) months,

ceil(mod(:days,365.25 / 12)) days

from dual

but dont be surprised

DAYSYEARSMONTHSDAYS
731201
73011130
366101
36501131
36401130
92031
910231
900230
62022
61021
600130
32012
31011
300030
1001

maybe next version looks better

select days,

floor(days / 365.25) years,

floor(mod(days,365.25) / (365.25 / 12)) months,

round(mod(days,365.25 / 12)) days

from periods

DAYSYEARSMONTHSDAYS
731201
73011130
366101
36501130
36401129
92031
910230
900229
62021
61020
600130
32012
31011
300030
1001

Regards

Etbin

• ###### 10. Re: Kindly suggest how to convert number into days,months,years
Currently Being Moderated

Hi Etbin,

Thanks a lot bro.

It worked 100%.

Regards,

Afzal.