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

    To find months and days between 2 dates

    SureshM

      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
          24c14495-35a9-4391-8c06-25ca6abf36bd

          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
            Hoek

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

              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
                BluShadow

                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.

                 

                Your example is also wrong.

                 

                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
                  SureshM

                  Hi Blu Shadow,

                   

                  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
                    BluShadow

                    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
                      BluShadow

                      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
                        SureshM

                        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
                          BluShadow

                          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
                            BluShadow

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

                              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
                                BluShadow

                                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
                                  SureshM

                                  Hi All,

                                   

                                  Thanks a lot for your reply.

                                   

                                  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