Skip to Main Content
• Questions

# Breadcrumb

## Question and Answer

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```

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
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
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