CODEWITHSUNDEEP
sqloracle-database
sonakshi sinha
sonakshi sinha

Reputation: 75

Calculate year from date difference in Oracle

I want to calculate the number of years between two dates.

eg :- Select to_date('30-OCT-2013') - TO_date('30-SEP-2014') FROM DUAL;

This would result to 335 days. I want to show this in years, which will be .97 years.

Upvotes: 3

Views: 15667

Answers (3)

Andrew Spencer
Andrew Spencer

Reputation: 16474

None of the methods proposed in the other answers give exactly the same answer, look:

with dates as ( select to_date('2013-10-01', 'YYYY-MM-DD') as date1, to_date('2014-09-01', 'YYYY-MM-DD') as date2 from dual)

select months_between(date2, date1)/12 as years_between, 'months_between(date1, date2)' as method from dates
union
select (date2 - date1)/365.242199, '(date2 - date1) / 365.242199' from dates
union
select extract(year from date2) - extract(year from date1), 'extract(year) from date2 - extract(year from date1)' from dates
union
select (date2 - date1) / (ADD_MONTHS(date1 ,12) - date1), '(nb days between date1 and date2) / (nb days in 1 year starting at date1)' from dates
;

gives

YEARS_BETWEEN                               METHOD

0.9166666666666666666666666666666666666667  months_between(date1, date2)
0.9171996032145234127231831719422979380321  (date2 - date1) / 365.242199
0.9178082191780821917808219178082191780822  nb days date2-date1 / (nb days in 1 year starting at date1)
1                                           extract(year) from date2 - extract(year from date1)

Why? Because they are all answering slightly different questions.

  • MONTHS_BETWEEN gives the number of whole months between the 2 dates, and calculates the fractional part as the remainder in days divided by 31.
  • dividing by 365.242199 assumes that you want the number of solar years between 00:00 on the first date and 00:00 on the second date, to 9 significant figures.
  • the third method assumes you want to calculate how many calendar days between the two dates, relative to the number of calendar days in the specific year that started on the first date (so the same number of calendar days will give you a different number of years, depending on whether there's a leap day between date1 and the same date on the following year).
  • the extract(year) approach assumes you want know the difference in whole numbers between the calendar year of the first date and the calendar year of the second date

It's not possible to answer the question perfectly, without knowing which kind of year we are talking about. Do we mean a solar year, or a calendar year, and if we mean a calendar year, do we we want to calculate by months (as if all months were the same length, which they aren't) or by the actual number of days between those dates and in that specific year?

Indeed, if we're talking about calendar years, it's not possible to calculate a fractional number of years in a consistent way at all, since the concept "calendar year" doesn't correspond to a fixed number of days.

The good news is that (aside from the fourth method) all the approaches give the same answer to the first 2 significant figures, as DCookie said. So you can save worrying about what you mean when you say "year", and instead start to think of other concerns such as performance, portability, readability... which also are quite different between these approaches.

I do think though, that whenever a non-programmer asks for something like "the fractional number of years between two dates," they should be punished by being given a detailed explanation of the different ways to calculate it, and why and how they are different, until they agree that it would be better expressed in number of weeks (which at least have the benefit of containing a fixed number of days).

Upvotes: 0

DCookie
DCookie

Reputation: 43523

I don't know how you figure that's .97 years. Here's what I get:

SQL> SELECT  ( TO_date('30-SEP-2014') - to_date('30-OCT-2013')) /
                (ADD_MONTHS(DATE '2013-10-30',12) - DATE '2013-10-30') "Year Fraction" 
       FROM DUAL;

Year Fraction
-------------
0.91780821917

You're going to have to pick a date to base your year calculation on. This is one way to do it. I chose to make a year be the number of days between 10/30/2013 and 10/30/2014. You could also make it a year between 9/30/2013 and 9/30/2014.

As an aside, if you're only interested in 2 decimal places, 365 is pretty much as good as 366.

UPDATE: Used ADD_MONTHS in calculating the denominator. That way you can use the same date for the entire calculation of the number of days in a year.

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172378

Simply do this(divide by 365.242199):

Select (to_date('30-SEPT-2014') - TO_date('30-OCT-2013'))/365.242199 FROM DUAL;

1 YEAR = 365.242199 days

OR

Try something like this using MONTHS_BETWEEN:-

select floor(months_between(date '2014-10-10', date '2013-10-10') /12) from dual;

or you may also try this:-

SELECT EXTRACT(YEAR FROM date1) - EXTRACT(YEAR FROM date2) FROM DUAL;

On a side note:-

335/365.242199 = 0.917199603 and not .97

Upvotes: 4

Related Questions