1 2 Previous Next 21 Replies Latest reply: Jan 15, 2013 5:12 AM by 877722

# How to get days and months when two dates are given

Hi All,

I have a requirement where I need to return the number of months and days between given dates.
I dont need to take the year into account as the dates difference is always less than an year

I have 28-jun-2012 and 31-jan-2013 as dates.

I'm working on 10g

when I do select months_between(:a,:b) from dual and provide those dates it gives me 7.09677
I want it to give as 7 months and 9 days or what ever the exact days are.

Your inputs are much appreciated
• ###### 1. Re: How to get days and months when two dates are given
How did you get 7 months 9 days? I would say 7 months 3 days. 0.096 x 30 days
• ###### 2. Re: How to get days and months when two dates are given
Try this:
``````WITH t AS
(SELECT TO_DATE('28-JUN-2012', 'DD-MON-YYYY') date1, TO_DATE('31-JAN-2013', 'DD-MON-YYYY') date2 FROM dual)
SELECT TRUNC(MONTHS_BETWEEN(date2, date1)) months,
date2 - ADD_MONTHS(date1, TRUNC(MONTHS_BETWEEN(date2, date1))) days
FROM   t``````
• ###### 3. Re: How to get days and months when two dates are given
You'll have a difficulty with using months_between, because Oracle 'looks' to see if the dates are the last day of the month and if so, takes them as whole months difference even if the days are different...

In which case you need to do some manual style calculations such as... (not completely tested, but just as an idea)...
``````SQL> ed
Wrote file afiedt.buf

1  with t as (select date '2012-06-28' as start_date, date '2013-01-31' as end_date from dual union all
2             select date '2012-02-29', date '2012-06-30' from dual)
3  --
4  select start_date, end_date
5        ,months_between(end_date,start_date) as mnth_bet
6        ,case when to_number(to_char(end_date,'DD')) >= to_number(to_char(start_date,'DD')) then
7           floor(months_between(end_date,start_date))
8         else
9           floor(months_between(end_date,start_date))-1
10         end as mnth
11        ,case when to_number(to_char(end_date,'DD')) >= to_number(to_char(start_date,'DD')) then
12           to_number(to_char(end_date,'DD')) - to_number(to_char(start_date,'DD'))
13         else
14           (to_number(to_char(last_day(start_date),'DD'))-to_number(to_char(start_date,'DD')))+
15           (to_number(to_char(start_date,'DD')))
16         end as dys
17* from t
SQL> /

START_DATE           END_DATE               MNTH_BET       MNTH        DYS
-------------------- -------------------- ---------- ---------- ----------
28-JUN-2012 00:00:00 31-JAN-2013 00:00:00 7.09677419          7          3
29-FEB-2012 00:00:00 30-JUN-2012 00:00:00          4          4          1``````
• ###### 4. Re: How to get days and months when two dates are given
lee200 wrote:
Try this:
``````WITH t AS
(SELECT TO_DATE('28-JUN-2012', 'DD-MON-YYYY') date1, TO_DATE('31-JAN-2013', 'DD-MON-YYYY') date2 FROM dual)
SELECT TRUNC(MONTHS_BETWEEN(date2, date1)) months,
date2 - ADD_MONTHS(date1, TRUNC(MONTHS_BETWEEN(date2, date1))) days
FROM   t``````
You've fallen for the months_between rounding issue...
``````SQL> ed
Wrote file afiedt.buf

1  with t as (select date '2012-06-28' as start_date, date '2013-01-31' as end_date from dual union all
2             select date '2012-02-29', date '2012-06-30' from dual)
3  --
4  SELECT TRUNC(MONTHS_BETWEEN(end_date, start_date)) months,
5         end_date - ADD_MONTHS(start_date, TRUNC(MONTHS_BETWEEN(end_date, start_date))) days
6* from t
SQL> /

MONTHS       DAYS
---------- ----------
7          3
4          0``````
Most people would say that there is 4 months and 1 day between 29th Feb 2012 and 30th June 2012. etc.

Edited by: BluShadow on 14-Jan-2013 14:42

I see the OP has been too quick to mark your answer as correct... ah well.
• ###### 5. Re: How to get days and months when two dates are given
``````select TO_CHAR(diff, 'DD') - 1 as day
,TO_CHAR(diff, 'MM') - 1 as month
,TO_CHAR(diff, 'YYYY') - 1 as year
from
(    select dummy_date + ( TO_DATE(:a) - TO_DATE(:b) ) as diff from (
select to_Date('01.01.0001','DD.MM.YYYY')  as dummy_date from dual
)
)``````
• ###### 6. Re: How to get days and months when two dates are given
Correction to my own solution...
``````SQL> ed
Wrote file afiedt.buf

1  with t as (select date '2012-06-28' as start_date, date '2013-01-31' as end_date from dual union all
2             select date '2012-02-29', date '2012-06-30' from dual union all
3             select date '2012-02-29', date '2012-06-15' from dual
4            )
5  --
6  select start_date, end_date
7        ,months_between(end_date,start_date) as mnth_bet
8        ,floor(months_between(end_date,start_date)) as mnth
9        ,case when to_number(to_char(end_date,'DD')) >= to_number(to_char(start_date,'DD')) then
10           to_number(to_char(end_date,'DD')) - to_number(to_char(start_date,'DD'))
11         else
12           (to_number(to_char(last_day(start_date),'DD'))-to_number(to_char(start_date,'DD')))+
13           (to_number(to_char(end_date,'DD')))
14         end as dys
15* from t
SQL> /

START_DATE           END_DATE               MNTH_BET       MNTH        DYS
-------------------- -------------------- ---------- ---------- ----------
28-JUN-2012 00:00:00 31-JAN-2013 00:00:00 7.09677419          7          3
29-FEB-2012 00:00:00 30-JUN-2012 00:00:00          4          4          1
29-FEB-2012 00:00:00 15-JUN-2012 00:00:00  3.5483871          3         15``````
• ###### 7. Re: How to get days and months when two dates are given
Bawer wrote:
``````select TO_CHAR(diff, 'DD') - 1 as day
,TO_CHAR(diff, 'MM') - 1 as month
,TO_CHAR(diff, 'YYYY') - 1 as year
from
(    select dummy_date + ( TO_DATE(:a) - TO_DATE(:b) ) as diff from (
select to_Date('01.01.0001','DD.MM.YYYY')  as dummy_date from dual
)
)``````
Interesting idea... not quite sure it works though...
``````SQL> ed
Wrote file afiedt.buf

1  with t as (select date '2012-06-28' as start_date, date '2013-01-31' as end_date from dual union all
2             select date '2012-02-29', date '2012-06-30' from dual union all
3             select date '2012-02-29', date '2012-06-15' from dual
4            )
5  --
6  select start_date, end_date
7        ,TO_CHAR(diff, 'YYYY') - 1 as year
8        ,TO_CHAR(diff, 'MM') - 1 as month
9        ,TO_CHAR(diff, 'DD') - 1 as day
10  from
11  (    select start_date, end_date, to_Date('01.01.0001','DD.MM.YYYY') + ( TO_DATE(end_date) - TO_DATE(start_date) ) as diff
12       from t
13* )
SQL> /

START_DATE           END_DATE                   YEAR      MONTH        DAY
-------------------- -------------------- ---------- ---------- ----------
28-JUN-2012 00:00:00 31-JAN-2013 00:00:00          0          7          5
29-FEB-2012 00:00:00 30-JUN-2012 00:00:00          0          4          2
29-FEB-2012 00:00:00 15-JUN-2012 00:00:00          0          3         17``````
• ###### 8. Re: How to get days and months when two dates are given
Hi,

Here's one way. It makes a first approximation, assuming that the difference will just be the difference in months and the difference in days. If the difference in days is negative, then it needs to make an adjustment, which is what the main query is for:
``````WITH     first_pass     AS
(
SELECT     start_dt
,     end_dt
,     MONTHS_BETWEEN ( TRUNC (end_dt,   'MONTH')
, TRUNC (start_dt, 'MONTH')
)                 AS raw_month_dif
,     EXTRACT (DAY FROM end_dt)
- EXTRACT (DAY FROM start_dt)     AS raw_day_dif
FROM       table_x
)
SELECT       start_dt, end_dt
,       CASE
WHEN  raw_day_dif >= 0
THEN  raw_month_dif
ELSE  raw_month_dif - 1
END      AS month_dif
,       CASE
WHEN  raw_day_dif >= 0
THEN  raw_day_dif
ELSE  raw_day_dif + EXTRACT (DAY FROM LAST_DAY (start_dt))
END     AS day_dif
FROM       first_pass
;``````
Depending on how you want to handle months of different lengths, you may want to change this some.

This works fine if end_dt is more that a year after start_dt; months_dif may be greater than 12 in that case.

Edited by: Frank Kulash on Jan 14, 2013 9:56 AM
• ###### 9. Re: How to get days and months when two dates are given
Correction to my own solution...
``````SQL> ed
Wrote file afiedt.buf

1  with t as (select date '2012-06-28' as start_date, date '2013-01-31' as end_date from dual union all
2             select date '2012-02-29', date '2012-06-30' from dual union all
3             select date '2012-02-29', date '2012-06-15' from dual
4            )
5  --
6  select start_date, end_date
7        ,months_between(end_date,start_date) as mnth_bet
8        ,floor(months_between(end_date,start_date)) as mnth
9        ,case when to_number(to_char(end_date,'DD')) >= to_number(to_char(start_date,'DD')) then
10           to_number(to_char(end_date,'DD')) - to_number(to_char(start_date,'DD'))
11         else
12           (to_number(to_char(last_day(start_date),'DD'))-to_number(to_char(start_date,'DD')))+
13           (to_number(to_char(end_date,'DD')))
14         end as dys
15* from t
SQL> /

START_DATE           END_DATE               MNTH_BET       MNTH        DYS
-------------------- -------------------- ---------- ---------- ----------
28-JUN-2012 00:00:00 31-JAN-2013 00:00:00 7.09677419          7          3
29-FEB-2012 00:00:00 30-JUN-2012 00:00:00          4          4          1
29-FEB-2012 00:00:00 15-JUN-2012 00:00:00  3.5483871          3         15``````
Thanks a lot,BluShadow for your answer and would you mind giving it for the year as well like 0 years 7 months 3 days?

Thanks again for your help

Edited by: 874719 on Jan 14, 2013 6:58 AM
• ###### 10. Re: How to get days and months when two dates are given
Yep, looks good to me Frank...
``````SQL> ed
Wrote file afiedt.buf

1  with t as (select date '2012-06-28' as start_dt, date '2013-01-31' as end_dt from dual union all
2             select date '2012-02-29', date '2012-06-30' from dual union all
3             select date '2012-02-29', date '2012-06-15' from dual
4            )
5  --
6  ,first_pass        AS
7  (
8     SELECT  start_dt
9     ,       end_dt
10     ,       MONTHS_BETWEEN ( TRUNC (end_dt,   'MONTH')
11                            , TRUNC (start_dt, 'MONTH')
12                            )                AS raw_month_dif
13     ,       EXTRACT (DAY FROM end_dt)
14           - EXTRACT (DAY FROM start_dt)     AS raw_day_dif
15     FROM    t
16  )
17  SELECT       start_dt, end_dt
18  ,    CASE
19           WHEN  raw_day_dif >= 0
20           THEN  raw_month_dif
21           ELSE  raw_month_dif - 1
22       END   AS month_dif
23  ,    CASE
24           WHEN  raw_day_dif >= 0
25           THEN  raw_day_dif
26           ELSE  raw_day_dif + EXTRACT (DAY FROM LAST_DAY (start_dt))
27       END   AS day_dif
28* FROM         first_pass
SQL> /

START_DT             END_DT                MONTH_DIF    DAY_DIF
-------------------- -------------------- ---------- ----------
28-JUN-2012 00:00:00 31-JAN-2013 00:00:00          7          3
29-FEB-2012 00:00:00 30-JUN-2012 00:00:00          4          1
29-FEB-2012 00:00:00 15-JUN-2012 00:00:00          3         15``````
• ###### 11. Re: How to get days and months when two dates are given
874719 wrote:
Thanks a lot,BluShadow for your answer and would you mind giving it for the year as well like 0 years 7 months 3 days?
Your original post said:
I dont need to take the year into account as the dates difference is always less than an year
Now you've got the idea, why not have a go yourself first, and then if you're still not getting it, post what you've tried so we can help you.
• ###### 12. Re: How to get days and months when two dates are given
Bawer wrote:
``````select TO_CHAR(diff, 'DD') - 1 as day
,TO_CHAR(diff, 'MM') - 1 as month
,TO_CHAR(diff, 'YYYY') - 1 as year
from
(    select dummy_date + ( TO_DATE(:a) - TO_DATE(:b) ) as diff from (
select to_Date('01.01.0001','DD.MM.YYYY')  as dummy_date from dual
)
)``````
Interesting idea... not quite sure it works though...
yes, my mistake, here is corrected version:
``````select TO_CHAR(diff, 'DD')  as day
,TO_CHAR(diff, 'MM')  as month
,TO_CHAR(diff, 'YYYY') - 1 as year
from
(    select  ( (dummy_date + ( TO_DATE('31.01.2013') - TO_DATE('28.06.2012')  ) ) - interval '1' day  )  - interval '1' month as diff from (
select to_Date('01.01.0001','DD.MM.YYYY')  as dummy_date from dual
)
);``````
• ###### 13. Re: How to get days and months when two dates are given
874719 wrote:
Thanks a lot,BluShadow for your answer and would you mind giving it for the year as well like 0 years 7 months 3 days?
Your original post said:
I dont need to take the year into account as the dates difference is always less than an year
Now you've got the idea, why not have a go yourself first, and then if you're still not getting it, post what you've tried so we can help you.
Oh... go on... I'm in a good mood today...
``````SQL> ed
Wrote file afiedt.buf

1  with t as (select date '2012-06-28' as start_date, date '2013-01-31' as end_date from dual union all
2             select date '2012-02-29', date '2012-06-30' from dual union all
3             select date '2010-02-28', date '2012-06-30' from dual union all
4             select date '2012-02-29', date '2012-06-15' from dual
5            )
6  --
7  select start_date, end_date
8        ,months_between(end_date,start_date) as mnth_bet
9        ,floor(months_between(end_date,start_date)/12) as yr
10        ,floor(mod(months_between(end_date,start_date),12)) as mnth
11        ,case when to_number(to_char(end_date,'DD')) >= to_number(to_char(start_date,'DD')) then
12           to_number(to_char(end_date,'DD')) - to_number(to_char(start_date,'DD'))
13         else
14           (to_number(to_char(last_day(start_date),'DD'))-to_number(to_char(start_date,'DD')))+
15           (to_number(to_char(end_date,'DD')))
16         end as dys
17* from t
SQL> /

START_DATE           END_DATE               MNTH_BET         YR       MNTH        DYS
-------------------- -------------------- ---------- ---------- ---------- ----------
28-JUN-2012 00:00:00 31-JAN-2013 00:00:00 7.09677419          0          7          3
29-FEB-2012 00:00:00 30-JUN-2012 00:00:00          4          0          4          1
28-FEB-2010 00:00:00 30-JUN-2012 00:00:00         28          2          4          2
29-FEB-2012 00:00:00 15-JUN-2012 00:00:00  3.5483871          0          3         15``````
• ###### 14. Re: How to get days and months when two dates are given
Bawer wrote:
yes, my mistake, here is corrected version:
Maybe it's something I'm doing, but still I get results that look wrong with your solution...
``````SQL> ed
Wrote file afiedt.buf

1  with t as (select date '2012-06-28' as start_date, date '2013-01-31' as end_date from dual union all
2             select date '2012-02-29', date '2012-06-30' from dual union all
3             select date '2012-02-29', date '2012-06-15' from dual
4            )
5  --
6  select start_date, end_date
7        ,TO_CHAR(diff, 'YYYY') - 1 as year
8        ,TO_CHAR(diff, 'MM')  as month
9        ,TO_CHAR(diff, 'DD')  as day
10  from
11  ( select  start_date, end_date
12           ,( (dummy_date + ( end_date - start_date  ) ) - interval '1' day  )  - interval '1' month as diff
13      from (
14        select to_Date('01.01.0001','DD.MM.YYYY')  as dummy_date from dual
15      ), t
16* )
SQL> /

START_DATE           END_DATE                   YEAR MO DA
-------------------- -------------------- ---------- -- --
28-JUN-2012 00:00:00 31-JAN-2013 00:00:00          0 07 05
29-FEB-2012 00:00:00 30-JUN-2012 00:00:00          0 04 02
29-FEB-2012 00:00:00 15-JUN-2012 00:00:00          0 03 17``````
1 2 Previous Next