This discussion is archived
1 2 3 33 Replies Latest reply: Oct 11, 2013 2:35 AM by SureshM Go to original post
• ###### 15. Re: To find months and days between 2 dates
Currently Being Moderated

You can count the actual months and days between two dates

select trunc(months_between(date2,date1)) months,

date2 - add_months(date1,trunc(months_between(date2,date1))) days

from (select to_date('25-Aug-2013','dd-Mon-yyyy') date1,to_date('23-Oct-2013','dd-Mon-yyyy') date2

from dual

)

MONTHSDAYS
128

Regards

Etbin

• ###### 16. Re: To find months and days between 2 dates
Currently Being Moderated

Hi Etbin,

Thanks for the reply.

But my scenario is not fullfilled with this query. We have to consider every month as 30days.

• ###### 17. Re: To find months and days between 2 dates
Currently Being Moderated

Hi All,

Kindly let me know if ypu want any other information.

Regards

Suresh

• ###### 18. Re: To find months and days between 2 dates
Currently Being Moderated

Hi,

Above query fails for

From Date : 01-Feb-2013

To Date     :31-May-2013

Regards

Suresh

• ###### 19. Re: To find months and days between 2 dates
Currently Being Moderated

SureshM wrote:

Hi All,

Kindly let me know if ypu want any other information.

Regards

Suresh

Yes, more clarification.

You say:

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

So what is it?  1 month or 30 days.  It can't be considered as both.

What is the exact rules?

What is the exact expected output?

And shouldn't anything over 30 days in the result be considered as another month? (as per the 1 month 58 days example we had before... why isn't that 2 months 28 days)?

Computers work on exact logic... not fluffy requirements.

• ###### 20. Re: To find months and days between 2 dates
Currently Being Moderated

Hi, Suresh,

What's wrong with the solution I posted in reply #4, above, that is

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

,  MOD  (date_2 - date_1,   30)   AS left_over

?  It treats the difference between any DATE and itself as 0 days, where (apparantly) you want to treat the difference between any DATE and itself as 1 day, but that's easy to fix: just add 1 before to the difference, like this:

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

,  MOD  (date_2 + 1 - date_1,   30)   AS left_over

If you want to call the first expression "months", then you can, but I don't think it's a good idea, because it's so far off from a month.

If not, post  a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.

Post your query, using the expressions above.  Point out where that query above is giving the wrong results, and explain, using specific examples, how you get the correct results from the given data in those places.

Always say which version of Oracle you're using (e.g., 11.2.0.2.0).

See the forum FAQ: https://forums.oracle.com/message/9362002

• ###### 21. Re: To find months and days between 2 dates
Currently Being Moderated

We have to consider every month as 30 days. So consider 30 days.

Regards

Suresh

• ###### 22. Re: To find months and days between 2 dates
Currently Being Moderated

Hi FrankKulash,

If I consider these two dates.

Date 1 : 25-Feb-2013

Date 2 : 23-Mar-2013

I should get

25-Feb-2013 to 30-Feb-2013 = 6 days  (Considering every month as 30 days)

01-mar-2013 to 23-mar-2013 = 23 days

Total = 29 days

where in your query I am getting 27 days.

Regards

Suresh

• ###### 23. Re: To find months and days between 2 dates
Currently Being Moderated

If I get output in days, Considering 30 days a month then it will be fine.

For Eg:

Date 1 : 25-Feb-2013

Date 2 : 31-Mar-2013

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

01-Mar-2013 to 31-Mar-2013 =  30 days

Total = 36 days

Regards

Suresh

• ###### 24. Re: To find months and days between 2 dates
Currently Being Moderated

Hi,

SureshM wrote:

Hi FrankKulash,

If I consider these two dates.

Date 1 : 25-Feb-2013

Date 2 : 23-Mar-2013

I should get

25-Feb-2013 to 30-Feb-2013 = 6 days  (Considering every month as 30 days)

01-mar-2013 to 23-mar-2013 = 23 days

Total = 29 days

where in your query I am getting 27 days.

Regards

Suresh

There is no February 30 in 2013.  In fact, there's no February 30 in any year.  Counting both the starting and the ending dates, it looks to me like 27 is the correct answerr.

Feb. 25 is the 1st day.

Feb. 26 is the 2nd day.

Feb. 27 is the 3rd day.

Feb. 28 is the 4th day.

March 1 is the 5th day.

March 2 is the 6th day.

...

March 22 is the 26th day.

March 27 is the 27th day.

Again, post CREATE TABLE and INSERT statements for som sample data and your complete query.  Say where the results of that query are wrong, and show how you compute the right results.

• ###### 25. Re: To find months and days between 2 dates
Currently Being Moderated

Hi FrankKulash,

Agreed to your point but as per our requirement we have to consider every month as 30 days.

Regards

Suresh

• ###### 26. Re: To find months and days between 2 dates
Currently Being Moderated

Hi All,

Kindly help.

Regards

Suresh

• ###### 27. Re: To find months and days between 2 dates
Currently Being Moderated

Hi, Suresh,

SureshM wrote:

Hi FrankKulash,

Agreed to your point but as per our requirement we have to consider every month as 30 days.

Regards

Suresh

Given that most months are not exactly 30 days, it's unclear exactly what the implications of your requirement are.

I'm not saying that you can't have consistent requirements like that that makes sense in your business.  I'm saying you can have hundreds of different ones, each of which might make sense in different businesses.  You don't want to test hundreds of solutions (that meet hundreds of different requirements) any more than I want to write them, so say what your requirements are.

One more time: post some sample data (CREATE TABLE and INSERT statements), the results you want from that data, and explain how you get those results from that data.

• ###### 28. Re: To find months and days between 2 dates
Currently Being Moderated

Consider the case where start_date is 28-Feb-2013 and end_date is the following day, 01-Mar-2013. According to your rules (I think), this would come out as a period of four days - which is simply  nonsense. Or the case where the start is 31-Oct-2013 and the end is 01-Nov-2013, I'm not sure what answer you're expecting here - maybe 1 day, since 31-Oct is assumed not to exist? What about a period that starts and ends on 31-Dec-2013? 0 days?

I know you're about to say "we have to consider every month as 30 days", but stop for a moment and consider - are you absolutely sure you're interpreting that requirement correctly? From whom has this requirement originated? Maybe you should double-check with them, pointing out the issues that arise, before pursuing this much further.

It's possible to calculate the real number of months and days between two dates, by use of the months_between function and some fancy footwork. It's still a bit fiddly, and you still have choices to make on exactly how the calculation should be made, but examples posted and linked above will do that job.

Or, one might decide to sweep aside the complications of actual month lengths, and assume that they're all of a standard length. The actual average length for a month is 30.4375 days, but 30 is close enough for a rule of thumb over a short period. So you could use Frank's TRUNC and MOD method to get a simple figure.

Now, the approach that you're slowly stumbling towards defining is way more complicated than working out the real number, and produces deeply problematic results. I would want to be absolutely sure that this was exactly what was required - and get a detailed written definition of the calculation to be followed to determine the month/day figures - before I put my name to any code that produced such nonsense.

My guess is that the TRUNC and MOD answers are the ones that are actually being looked for here - i.e. "month" is defined as a block of 30 days, and "days" is the days left over - rather than any particular ineraction with the calendar.

• ###### 29. Re: To find months and days between 2 dates
Currently Being Moderated

ChrisHunt wrote:

Consider the case where start_date is 28-Feb-2013 and end_date is the following day, 01-Mar-2013. According to your rules (I think), this would come out as a period of four days - which is simply  nonsense. Or the case where the start is 31-Oct-2013 and the end is 01-Nov-2013, I'm not sure what answer you're expecting here - maybe 1 day, since 31-Oct is assumed not to exist? What about a period that starts and ends on 31-Dec-2013? 0 days?

As I understand... 31st is considered same as 30, so 31-Oct-2013 to 01-Nov-2013 would be 2 days, just as 30-Oct-2013 to 01-Nov-2013 would also be 2 days.

As for start and end being 31-Dec-2013, that would be one day, as it's the same as 30-Dec-2013 for both start and end.

I know you're about to say "we have to consider every month as 30 days", but stop for a moment and consider - are you absolutely sure you're interpreting that requirement correctly? From whom has this requirement originated? Maybe you should double-check with them, pointing out the issues that arise, before pursuing this much further.

I agree, it's the soft of bizarre logic that would be thrown out by auditors in financing or suchlike.

1 2 3

#### Legend

• Correct Answers - 10 points