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
)
MONTHS | DAYS |
---|---|
1 | 28 |
Regards
Etbin
Hi Etbin,
Thanks for the reply.
But my scenario is not fullfilled with this query. We have to consider every month as 30days.
Hi All,
Kindly let me know if ypu want any other information.
Regards
Suresh
Hi,
Above query fails for
From Date : 01-Feb-2013
To Date :31-May-2013
Regards
Suresh
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.
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.
I hope this answers your question.
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
We have to consider every month as 30 days. So consider 30 days.
Regards
Suresh
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
Hi Blushadow,
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
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.
Hi FrankKulash,
Agreed to your point but as per our requirement we have to consider every month as 30 days.
Regards
Suresh
Hi All,
Kindly help.
Regards
Suresh
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.
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.
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.