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

        Hi All,

         

        Kindly help.

         

        Regards

        Suresh

        • 31. Re: To find months and days between 2 dates
          Ramin Hashimzadeh

          if i understood you correctly you need

           

          SQL>

          SQL> with t(d1,d2) as

            2  (

            3  select to_date('05-10-2013','dd-mm-yyyy'), to_date('31-08-2013','dd-mm-yyyy') from dual union all

            4  select to_date('31-03-2013','dd-mm-yyyy'), to_date('05-02-2013','dd-mm-yyyy') from dual

            5  )

            6  select  d1,d2, trunc(mn)||' month '||trunc(mod(mn,1)*30)||' days' res

            7   from (select d1,d2, (d1-d2)/30 mn,(d1-d2) dy from t

            8   )

            9  /

          D1          D2          RES

          ----------- ----------- --------------------------------------------------------------------------------

          05.10.2013  31.08.2013  1 month 5 days

          31.03.2013  05.02.2013  1 month 24 days

           

          SQL>

          SQL>

           

           

          ----

          Ramin Hashimzade

          • 32. Re: To find months and days between 2 dates
            davidp 2

            Assuming d2 > d1,

            with d as (

            select sysdate d1, sysdate + 56 d2 from dual

            union all select to_date('01-Mar-2013', 'dd-Mon-yyyy') d1, to_date('31-Mar-2013', 'dd-Mon-yyyy') d2 from dual

            union all select to_date('05-Feb-2013', 'dd-Mon-yyyy') d1, to_date('31-Mar-2013', 'dd-Mon-yyyy') d2 from dual

            union all select to_date('25-Feb-2013', 'dd-Mon-yyyy') d1, to_date('23-Mar-2013', 'dd-Mon-yyyy') d2 from dual

            union all select to_date('25-Feb-2013', 'dd-Mon-yyyy') d1, to_date('31-Mar-2013', 'dd-Mon-yyyy') d2 from dual

            union all select to_date('02-Aug-2013', 'dd-Mon-yyyy') d1, to_date('29-Oct-2013', 'dd-Mon-yyyy') d2 from dual

            union all select to_date('01-Feb-2013', 'dd-Mon-yyyy') d1, to_date('31-May-2013', 'dd-Mon-yyyy') d2 from dual

            union all select to_date('25-Aug-2013', 'dd-Mon-yyyy') d1, to_date('03-Sep-2013', 'dd-Mon-yyyy') d2 from dual

            union all select to_date('30-Jul-2013', 'dd-Mon-yyyy') d1, to_date('31-Aug-2013', 'dd-Mon-yyyy') d2 from dual

            union all select to_date('31-Jul-2013', 'dd-Mon-yyyy') d1, to_date('30-Aug-2013', 'dd-Mon-yyyy') d2 from dual

            union all select to_date('31-Jul-2013', 'dd-Mon-yyyy') d1, to_date('03-Aug-2013', 'dd-Mon-yyyy') d2 from dual

            union all select to_date('03-Jul-2013', 'dd-Mon-yyyy') d1, to_date('31-Aug-2013', 'dd-Mon-yyyy') d2 from dual

            union all  select to_date('31-08-2013','dd-mm-yyyy'), to_date('05-10-2013','dd-mm-yyyy') from dual

            union all  select to_date('05-02-2013','dd-mm-yyyy'), to_date('31-03-2013','dd-mm-yyyy') from dual

            union all  select to_date('05-02-2013','dd-mm-yyyy'), to_date('05-03-2013','dd-mm-yyyy') from dual

            union all  select to_date('05-02-2013','dd-mm-yyyy'), to_date('05-02-2013','dd-mm-yyyy') from dual

            )

            select d1, d2,

            1 + 30*trunc(months_between(d2, d1)) + LEAST(extract(day from d2), 30) - LEAST(extract(day from d1), 30)

              + CASE when extract(day from d2) < extract(day from d1) then 30 else 0 end  daysbetween

            from d

             

            D1          D2          DAYSBETWEEN

            ----------- ----------- -----------

            10-Oct-2013 05-Dec-2013          56

            01-Mar-2013 31-Mar-2013          30

            05-Feb-2013 31-Mar-2013          56

            25-Feb-2013 23-Mar-2013          29

            25-Feb-2013 31-Mar-2013          36

            02-Aug-2013 29-Oct-2013          88

            01-Feb-2013 31-May-2013         120

            25-Aug-2013 03-Sep-2013           9

            30-Jul-2013 31-Aug-2013          31

            31-Jul-2013 30-Aug-2013          31

            31-Jul-2013 03-Aug-2013           4

            03-Jul-2013 31-Aug-2013          58

            31-Aug-2013 05-Oct-2013          36

            05-Feb-2013 31-Mar-2013          56

            05-Feb-2013 05-Mar-2013          31

            05-Feb-2013 05-Feb-2013           1


            I think that matches your rules.

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

              Hi David,

               

              Thanks a lot. Your code is working fine.

               

              Regards

              Suresh

              1 2 3 Previous Next