Skip to Main Content
  • Questions

  • Convert an Integer into Years, Month and Days.

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Venkat.

Asked: September 16, 2016 - 7:31 pm UTC

Last updated: February 06, 2024 - 11:07 am UTC

Version: Oracle 11.2

Viewed 10K+ times! This question is

You Asked

Hello-

I am working on a seniority report, for which I need to compute number of years in employment but with the deduction in the number of days of un-paid absences. I have all the computations done, and have the number of days a employee is employed. Now I need to represent the number in terms of Years, Months and Days.

For example if the number of days is 1111 I need to 3 years, 0 months and 26 days (or what ever) as of today.

I have the following to compute off of date.


trunc(trunc(months_between (trunc(sysdate) , dtforcomp))/12) yrs,
mod(trunc(months_between(trunc(sysdate) , dtforcomp)), 12) mnths,
round( trunc(sysdate) - add_months(( dtforcomp), trunc(months_between(trunc(sysdate) , dtforcomp))),0) dys


Can I have something similar to compute off of an number?

Thanks
Venkat

and Connor said...

Just replace "dtforcomp" with "trunc(sysdate)-days" and you're done !

SQL> with ddd as
  2   ( select trunc(sysdate)-1111 dtforcomp from dual )
  3  select
  4  trunc(trunc(months_between (trunc(sysdate) , dtforcomp))/12) yrs,
  5  mod(trunc(months_between(trunc(sysdate) , dtforcomp)), 12) mnths,
  6  round( trunc(sysdate) - add_months(( dtforcomp), trunc(months_between(trunc(sysdate) , dtforcomp))),0) dys
  7  from ddd;

       YRS      MNTHS        DYS
---------- ---------- ----------
         3          0         15


Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you!

A reader, September 17, 2016 - 1:18 pm UTC

Will check it out!!

Thanks!

Return in 1 Column

Andrew, January 29, 2024 - 2:16 am UTC

to go from a number of days to years months days you can create a date add your days then subtract the original date to leave you your number of days as a date

SELECT to_char(add_months(to_date('01/01/0001', 'DD/MM/YYYY') + (11037/*number of days*/), -13)-1, 'YYYY-MM-DD') AS Calc_Years_Months_Days
FROM dual

Connor McDonald
January 29, 2024 - 4:04 am UTC

nifty trick

About the nifty trick

Stew Ashton, January 31, 2024 - 8:54 am UTC

Unfortunately it doesn't work if the number of days is less than 397.
SELECT 396 number_of_days,
to_char(add_months(to_date('01/01/0001', 'DD/MM/YYYY') + 396, -13)-1, 'sYYYY-MM-DD') AS Calc_Years_Months_Days
FROM dual
union all
SELECT 397 number_of_days,
to_char(add_months(to_date('01/01/0001', 'DD/MM/YYYY') + 397, -13)-1, 'sYYYY-MM-DD') AS Calc_Years_Months_Days
FROM dual;

NUMBER_OF_DAYS    CALC_YEARS_MONTHS_DAYS    
           396    -0001-12-31               
           397     0001-01-01
Best regards,
Stew
Chris Saxon
February 06, 2024 - 10:59 am UTC

Well spotted Stew!

timezone name

Arvind Mishra, February 01, 2024 - 12:52 am UTC

Hi,

I want to get 2024-02-01 00:01:20.840898 UTC instead of 2024-02-01 00:01:20.840898 +00:00 using select query. What format model I should use?

Thanks,
Arvind
Chris Saxon
February 06, 2024 - 11:07 am UTC

You can use the TZR format to get the name of a region. Note this only works if the timestamp uses a named time zone. If it's listed as the offset in hours/minutes, you'll get those:

alter session set time_zone = 'UTC';
select to_char ( current_timestamp, 'TZR TZH:TZM') from dual;

TO_CHAR(CURRENT_TIMESTAMP,'TZRTZH:TZM')    
UTC +00:00    

alter session set time_zone = '00:00';
select to_char ( current_timestamp, 'TZR TZH:TZM') from dual;

TO_CHAR(CURRENT_TIMESTAMP,'TZRTZH:TZM')
+00:00 +00:00

@Arvind: Time zone name

Stew Ashton, February 01, 2024 - 9:44 am UTC 

select systimestamp at time zone 'UTC' from dual
Best regards,
Stew
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.