1 2 3 Previous Next 33 Replies Latest reply on Oct 11, 2013 9:35 AM by SureshM

# To find months and days between 2 dates

Hi,

I want to find the months and days between 2 dates.

For Eg.

Date-1 : 25-Aug-2013

Date-2 : 23-Oct-2013

If we consider every month as 30 days it should give

25-Aug-2013 to 30-Aug-2013 = 6 days

01-Sep-2013 to 30-Sep-2013 = 1 Month

23-Oct-2013 to 30-Oct-2013 =   8 days

Total = 1 month and 14 days.

Kindly help at the earliest.

Thanks & Regards

Suresh

• ###### 1. Re: To find months and days between 2 dates

select to_date('03/14/2014','MM/DD/YYYY') - to_date('03/05/2014','MM/DD/YYYY') "Days" from dual;

Days

9

• ###### 3. Re: To find months and days between 2 dates

Here's an example that should get you going:

Getting the difference between dates

you'll need to adjust it a bit, ofcourse.

• ###### 4. Re: To find months and days between 2 dates

Hi,

If date_1 and date_2 are DATEs, then

date_2 - date_1

is the number of days between date_1 and date_2.  If you want to display 2 numbers

(1) the number above dived by 30 (rounded down to an integer), and

(2) the remainder when dividing the number above by 30

then one way to do it is:

TRUNC ((date_2 - date_1) / 30)   AS periods_of_30

,  MOD  (date_2 - date_1,   30)   AS left_over

• ###### 5. Re: To find months and days between 2 dates

SureshM wrote:

Hi,

I want to find the months and days between 2 dates.

For Eg.

Date-1 : 25-Aug-2013

Date-2 : 23-Oct-2013

If we consider every month as 30 days it should give

25-Aug-2013 to 30-Aug-2013 = 6 days

01-Sep-2013 to 30-Sep-2013 = 1 Month

23-Oct-2013 to 30-Oct-2013 =   8 days

Total = 1 month and 14 days.

Kindly help at the earliest.

Thanks & Regards

Suresh

That's not a good idea though.  Be considering every month as 30 days, then comparisons over larger date ranges (years) will be out by more and more days the larger the difference gets.

For Eg.

Date-1 : 25-Aug-2013

Date-2 : 23-Oct-2013

If we consider every month as 30 days it should give

25-Aug-2013 to 30-Aug-2013 = 6 days

01-Sep-2013 to 30-Sep-2013 = 1 Month

23-Oct-2013 to 30-Oct-2013 =   8 days

The last one should be:

01-Oct-2013 to 23-Oct-2013 = 23 days

giving a result of 1 month and 29 days.

Oracle provides a months_between function to do the calculation.

SQL> select months_between(date '2013-10-23', date '2013-08-25') from dual;

MONTHS_BETWEEN(DATE'2013-10-23',DATE'2013-08-25')
-------------------------------------------------
1.93548387

But of course, because the number of days in a month varies, it's not exacly known what the decimal part of the number represents.

However, if you combine methods, using months_between to get the months, and then assume 30 days for a month to get the days part from the remainder, it's more consistent over longer periods...

SQL> ed
Wrote file afiedt.buf

1  with dates as (select date '2013-08-25' as date_from, date '2013-10-23' as date_to from dual)
2  --
3  select months_between(date_to, date_from)
4        ,trunc(months_between(date_to, date_from)) as months
5        ,round(mod(months_between(date_to, date_from),1)*30) as days
6* from dates
SQL> /

MONTHS_BETWEEN(DATE_TO,DATE_FROM)     MONTHS       DAYS
--------------------------------- ---------- ----------
1.93548387          1         28

• ###### 6. Re: To find months and days between 2 dates

Apologize for the mistake, Instead of 23-Oct-2013 to 30-Oct-2013 =   8 days it should be 01-Oct-2013 to 23-Oct-2013 =   23 days

But If my dates are

Date 1 : 02-Aug-2013

Date 2 : 29-Oct-2013

then it should show

02-Aug-2013 to 30-Aug-2013 = 29 days

01-Sep-2013 to 30-Sep-2013 = 1 month

01-Oct-2013 to 29-Oct-2013 = 29 days

Total = 1 month 58 days

As per the requirement we have to take 1 month as 30 days so I have considered as 30 days.

Regards

Suresh

• ###### 7. Re: To find months and days between 2 dates

What if one of the dates is the 31st of the month?  How many days does that account for?  -1?

• ###### 8. Re: To find months and days between 2 dates

Something along these lines:

SQL> ed
Wrote file afiedt.buf

1  with dates as (select date '2013-08-02' as date_from, date '2013-10-29' as date_to from dual)
2  --
3  select trunc(months_between(trunc(date_to,'MM'), trunc(add_months(date_from-1,1),'MM'))) as months
4        ,(31-extract(day from date_from))+extract(day from date_to) as days
5* from dates
SQL> /

MONTHS       DAYS
---------- ----------
1         58

• ###### 9. Re: To find months and days between 2 dates

Hi,

31st will be considered as 1 day as we are calculating days on 30 days basis.

• ###### 10. Re: To find months and days between 2 dates

So you're saying that the 30th and the 31st will both be considered as 1 day?

• ###### 11. Re: To find months and days between 2 dates

Here we go... treating day 31 as if it's 30...

SQL> ed
Wrote file afiedt.buf

1  with dates as (select date '2013-08-02' as date_from, date '2013-10-31' as date_to from dual)
2  --
3  select trunc(months_between(trunc(date_to,'MM'), trunc(add_months(date_from-1,1),'MM'))) as months
4        ,(31-nvl(nullif(extract(day from date_from),31),30))+nvl(nullif(extract(day from date_to),31),30) as days
5* from dates
SQL> /

MONTHS       DAYS
---------- ----------
1         59

• ###### 12. Re: To find months and days between 2 dates

Maybe you could give us a table of expected results - in the form start_date, end_date, months, days - so we (and maybe you) can better understand the rule you're required to apply.

From your description, it appears to be something like this:

If the two dates are in the same month, the number of days is the difference between the two (inclusive) and the number of months is 0.

If the two dates are in different months, the number of days is (number of days between start_date and end of the month) + (number of days between start of the month and end date). The number of months is the number of whole months in between the month of the start date and the month of the end date.

I don't see where the "month = 30 days" comes into the above calculation - so if that's part of your specification, you might want to double-check that you've properly understood how the result should be calculated. A result of "1 month, 58 days" doesn't look to me like a proper (or useful) return from a function that's supposed to count generic 30-day months and days.

• ###### 13. Re: To find months and days between 2 dates

ChrisHunt wrote:

Maybe you could give us a table of expected results - in the form start_date, end_date, months, days - so we (and maybe you) can better understand the rule you're required to apply.

From your description, it appears to be something like this:

If the two dates are in the same month, the number of days is the difference between the two (inclusive) and the number of months is 0.

If the two dates are in different months, the number of days is (number of days between start_date and end of the month) + (number of days between start of the month and end date). The number of months is the number of whole months in between the month of the start date and the month of the end date.

Yeah, that sounds like my understanding.  It's the number of complete months between the two dates and then the days is the total days in the partial months at the start and end, assuming that 31 days is the same as 30.

I don't see where the "month = 30 days" comes into the above calculation - so if that's part of your specification, you might want to double-check that you've properly understood how the result should be calculated. A result of "1 month, 58 days" doesn't look to me like a proper (or useful) return from a function that's supposed to count generic 30-day months and days.

I agree, I don't see how that can be a reasonable output for a business.  Depending on the dates supplied it would be possible to get both:

1 month, 58 days

and

2 months, 28 days.

Which in priniciple are the same thing.

However, getting the exact rules from this OP seems to be like extracting blood out of a stone.

• ###### 14. Re: To find months and days between 2 dates

Hi All,

My requirement is as follows,

We have to consider every month as 30 days.

For eg-1:

Date1 : 31-Aug-2013

Date2 : 05-Oct-2013

31-Aug-2013 will be considered as 1 day and (if its 30-Aug-2013 it will be considered as 1 day and if its 29-Aug-2013 it will be considered as 2 days)

01-Sep-2013 to 30-Sep-2013 = 1 month (If this month has 31 days we will consider it as 30 as we are not counting days from this month we are taking as 1 month)

01-Oct-2013 to 05-Oct-2013 = 5 days

Eg-2:

Date1 : 05-Feb-2013

Date2 : 31-Mar-2013

05-Feb-2013 to 30-Feb-2013 = 26 days

01-Mar-2013 to 31-Mar-2013 = 1 month or 30 days

Regards

Suresh

1 2 3 Previous Next