CODEWITHSUNDEEP
sqloracle-databaseplsql
odew
odew

Reputation: 581

Difference between two dates in years, same month and same day

+------------+------------+------+
| start_date |  end_date  | year |
+------------+------------+------+
| 01/01/2001 | 01/01/2002 | 1    |
| 01/01/2001 | 04/01/2002 | 1    |
| 01/01/2001 | 31/12/2001 | 1    |
| 01/01/2001 | 03/01/2011 | 10   |
| 01/01/2001 | 02/01/2101 | 100  |
+------------+------------+------+

Having two dates in oracle with the following values:

+------------+------------+
| start_date |  end_date  |
+------------+------------+
| 01/01/2001 | 01/01/2002 |
+------------+------------+

I'm using the mod to calculate if difference is one year. MOD(end_date-start_date, 365) = 0

This works for small differences in dates, after 4 years I can't use zero and for 

start_date  end_date
01/01/2001  01/01/2101

end_date-start_date = 36524
end_date-start_date !=  365 * 100 (36500)

MOD(36500, 365) = 0
but 
MOD(36524, 365) = 24

The aim is to understand if the years are inside the interval around 2 year, around 3 year, around 4 ....

+------------+------------+
| start_date |  end_date  | year between 
+------------+------------+
| 01/06/2013 | 01/06/2015 | 2
+------------+------------+
+------------+------------+
| 01/06/2013 | 08/08/2015 | NA
+------------+------------+
+------------+------------+
| 31/05/2013 | 02/06/2015 | 2
+------------+------------+    
+------------+------------+
| 25/05/2013 | 02/06/2015 | NA
+------------+------------+

Any ideas ? I will try to use :

MOD(end_date-start_date, 365.242199)

After testing the MODULE option I decided to go with the following solution idea provided by dnoeth:

case when end_date between 
  add_months(start_date, round(months_between(end_date, start_date)/12)*12) - 5 
and   
  add_months(start_date, round(months_between(end_date, start_date)/12)*12) + 5    
then 
  round(months_between(end_date, start_date) / 12)

Thanks,

Upvotes: 0

Views: 481

Answers (2)

dnoeth
dnoeth

Reputation: 60462

If you want an approximate year (5 days less is still considered to be one year) you might simply add five days to your end date and use months_between:

TRUNC(MONTHS_BETWEEN(end_date+5, start_date)/12)

Edit: After adding more info I think this might work: If it's plus/minus five days based on the strat_date

case when add_months(start_date, trunc(months_between(end_date+5, start_date) / 12)*12) 
            between end_date - 5 and end_date + 5
     then trunc(months_between(end_date+5, start_date) / 12) 
end

Upvotes: 2

Mikhail
Mikhail

Reputation: 1560

Why not use simple MONTHS_BETWEEN function? For example:

SELECT TRUNC(MONTHS_BETWEEN(end_date, start_date)/12) FROM TTABLE

I.e.:

WITH dates AS (SELECT TO_DATE('01.01.2001','DD.MM.YYYY') START_DATE,
                      TO_DATE('01.01.2003','DD.MM.YYYY') END_DATE 
               FROM DUAL)
SELECT START_DATE, END_DATE, TRUNC(MONTHS_BETWEEN(END_DATE, START_DATE)/12) YEARS_BETWEEN FROM dates

Upvotes: 1

Related Questions