2

I am working on a program that takes takes a start date and an end date and returns separate values for Years, Months, and Days. Each category has its own section of code, this is what I have been using and so far its been semi-accurate until days (and sometimes months.I am not even trying to fool with leap-year at this point)

Ex Start: 04/10/2000 End: 04/10/2006 should give me 6 years 0 months and 0 days.

Years Code:

SELECT
 trunc(months_between((to_date(:main_DT_DateEnd1,'MM/DD/YYYY')),(to_date(:main_DT_DateBeg1,'MM/DD/YYYY'))) / 12) as "Years1"
FROM dual

Months Code:

SELECT
  trunc(mod(months_between((to_date(:main_DT_DateEnd1,'MM/DD/YYYY')),(to_date(:main_DT_DateBeg1,'MM/DD/YYYY'))), 12)) as "Months1"
FROM dual

Days Code: I have tried multiple versions of these without much success for example I can calculate total days between days but since there are different months in certain days dividing becomes more of a hassle. This is the closest one I am getting where if the days are the same then no calculation is needed, else subtract them using a substring. 1)

SELECT

CASE
 WHEN substr((to_date(:main_DT_DateBeg1,'MM/DD/YYYY')),4,5) = substr((to_date(:main_DT_DateEnd1,'MM/DD/YYYY')),4,5)
 THEN 0

 WHEN substr((to_date(:main_DT_DateBeg1,'MM/DD/YYYY')),4,5) <  substr((to_date(:main_DT_DateEnd1,'MM/DD/YYYY')),4,5)
 THEN to_number(substr((to_date(:main_DT_DateEnd1,'MM/DD/YYYY')),4,5)) -  to_number(substr((to_date(:main_DT_DateBeg1,'MM/DD/YYYY')),4,5))

END as "Days_1"

FROM dual

Thanks for your time, for those of you wondering this is for a job experience calculator :)

Taku_
  • 1,505
  • 3
  • 14
  • 22
  • 4
    I asked the same question http://stackoverflow.com/questions/11500098/get-the-difference-between-two-dates-both-in-months-and-days-in-sql and got very good answers – Stanley Mungai Sep 24 '14 at 13:52
  • Does this help https://community.oracle.com/thread/826651?start=0&tstart=0 – Lalit Kumar B Sep 24 '14 at 13:52
  • I will look into this stanley I have a similar answer using a different month format (maybe that's the difference) and Thanks lalit I will look into this one as well. I tried the 6 post response in that thread and it was really off. Thanks for the help so far! – Taku_ Sep 24 '14 at 14:12
  • @Stanley I got the answers I wanted from your thread , thank you – Taku_ Sep 24 '14 at 18:03

1 Answers1

0

Please refer here for examples: http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements001.htm#SQLRF50992

You can definitely use following logic to calculate years and months. In terms of the days - it's a bit tricky as for 28 Feb + 1 month returns 31 March...

select extract(year from (d2 - d1) year to month), 
       extract(month from (d2 - d1) year to month),
       add_months(d1, extract(year from (d2 - d1) year to month)*12 + extract(month from (d2 - d1) year to month)) - d2 
from (
select date'2014-08-27' as d2, date'2002-02-27' as d1
  from dual
)
Rusty
  • 1,988
  • 10
  • 12