Thanks for the question, Lloyd.
Asked: June 26, 2006 - 5:54 pm UTC
Last updated: June 28, 2006 - 5:00 pm UTC
Version: 9i
Viewed 1000+ times
You Asked
Hi Tom,
I am trying to get the longest non-decimal date period(Year, Month) between 2 dates.
--------------------------------------------------------
ADD: By Longest period I mean, YEAR is the longest date period, MONTH is the second longest date period and then DAY is the next.
I am not worried about hour, second or minutes.
When I find the difference between 2 dates;
If my answer is 12 months, (to get 12 months I need to add 1 day to the end date, otherwise I will get 11.93...) I want this in year which 1 YEAR. And I do not have a decimal part in the year part. Its a whole year, no additional months, no additional days.
If my answer is 13 months, the answer is 1 Year and 1 Month. Since I could not get a whole year(this might be 1.1 year or so) I want the answer as 13 Months.
If the difference between 2 dates are 1 year 3 months and 20 days, My answer should be in DAYS, the reason being I do not have a non-fractional year or a non-fractional month. So my requirement is to convert them as DAYS like 365+90+10 = 465 days would be my answer.
Is there a way to achieve this using SQL/PL/SQL. Hope this is clear this time.
--------------------------------------------------------------
For eg:-
1.
The difference between '30-APR-2007' and '01-MAY-2006' is 12 months. And 12 months = 1 Year. I want 1 year here.
2.
The difference between '31-MAY-2007' and '01-MAY-2006' is 13 months. I want 13 months to be the answer here rather than 1 year and 1 months.
3.
The difference between '15-MAY-2007' and '01-MAY-2006' is 379 days. I want this to be 379 days instead of having 1 year 0 months and 15 days.
Please help me in achieving this.
I do not want to get fraction of an year or month.
If I am getting fraction of an year I want them to be converted as months. And If I am getting fraction of month I want them to be converted as whole days.
I read your date difference functions. I understand the number of days in a month = (unknown).
4.
SELECT months_between('30-APR-2007', '01-MAY-2006')
FROM dual;
I am getting:
MONTHS
----------
11.9354839
Shouldn't this be equal to 12 months in real life scenario. Is there a way I can this as 12 months.
Could you please advise ?
Thanks
Lloyd
and Tom said...
ops$tkyte@ORA9IR2> select dt1, dt2, months_between(dt1+1,dt2), dt1-dt2
2 from t;
DT1 DT2 MONTHS_BETWEEN(DT1+1,DT2) DT1-DT2
--------- --------- ------------------------- ----------
30-APR-07 01-MAY-06 12 364
31-MAY-07 01-MAY-06 13 395
15-MAY-07 01-MAY-06 12.483871 379
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select dt1, dt2,
2 case when mod(months_between(dt1+1,dt2),12) = 0
3 then months_between(dt1+1,dt2)/12 || ' years'
4 when months_between(dt1+1,dt2) = trunc(months_between(dt1+1,dt2))
5 then months_between(dt1+1,dt2) || ' months'
6 else dt1-dt2 || ' days'
7 end str
8 from t;
DT1 DT2 STR
--------- --------- -----------------------------------------------
30-APR-07 01-MAY-06 1 years
31-MAY-07 01-MAY-06 13 months
15-MAY-07 01-MAY-06 379 days
for #4, no - maybe this makes it "more clear" why not:
ops$tkyte@ORA9IR2> SELECT months_between('30-APR-2007', '01-MAY-2006')
2 FROM dual
3 /
MONTHS_BETWEEN('30-APR-2007','01-MAY-2006')
-------------------------------------------
11.9354839
ops$tkyte@ORA9IR2> SELECT months_between(to_date('30-APR-2007 23:59:59','dd-mon-yyyy hh24:mi:ss'), '01-MAY-2006') btwn
2 FROM dual
3 /
BTWN
----------
11.9677416
you are CLOSE to a month... Add just one more SECOND:
ops$tkyte@ORA9IR2> SELECT months_between(to_date('30-APR-2007 23:59:59','dd-mon-yyyy hh24:mi:ss')+1/24/60/60, '01-MAY-2006') btwn 2 FROM dual
3 /
BTWN
----------
12
and you are now 12 months apart....
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment