1 2 3 Previous Next 33 Replies Latest reply: Oct 11, 2013 4:35 AM by SureshM Go to original post RSS
      • 15. Re: To find months and days between 2 dates
        Etbin

        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
          SureshM

          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
            SureshM


            Hi All,

             

            Kindly let me know if ypu want any other information.

             

            Regards

            Suresh

            • 18. Re: To find months and days between 2 dates
              SureshM

              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
                BluShadow

                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
                  Frank Kulash

                  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

                  • 21. Re: To find months and days between 2 dates
                    SureshM

                    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
                      SureshM

                      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
                        SureshM

                        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

                        • 24. Re: To find months and days between 2 dates
                          Frank Kulash

                          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
                            SureshM

                            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
                              SureshM

                              Hi All,

                               

                              Kindly help.

                               

                              Regards

                              Suresh

                              • 27. Re: To find months and days between 2 dates
                                Frank Kulash

                                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
                                  Chris Hunt

                                  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
                                    BluShadow

                                    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.