1 2 Previous Next 21 Replies Latest reply: Jan 15, 2013 5:12 AM by 877722 RSS

    How to get days and months when two dates are given

    877722
      Hi All,

      I have a requirement where I need to return the number of months and days between given dates.
      I dont need to take the year into account as the dates difference is always less than an year

      I have 28-jun-2012 and 31-jan-2013 as dates.

      I'm working on 10g

      when I do select months_between(:a,:b) from dual and provide those dates it gives me 7.09677
      I want it to give as 7 months and 9 days or what ever the exact days are.

      Your inputs are much appreciated
        • 1. Re: How to get days and months when two dates are given
          user503635
          How did you get 7 months 9 days? I would say 7 months 3 days. 0.096 x 30 days
          • 2. Re: How to get days and months when two dates are given
            lee200
            Try this:
            WITH t AS
            (SELECT TO_DATE('28-JUN-2012', 'DD-MON-YYYY') date1, TO_DATE('31-JAN-2013', 'DD-MON-YYYY') date2 FROM dual)
            SELECT TRUNC(MONTHS_BETWEEN(date2, date1)) months,
                   date2 - ADD_MONTHS(date1, TRUNC(MONTHS_BETWEEN(date2, date1))) days
            FROM   t
            • 3. Re: How to get days and months when two dates are given
              BluShadow
              You'll have a difficulty with using months_between, because Oracle 'looks' to see if the dates are the last day of the month and if so, takes them as whole months difference even if the days are different...

              In which case you need to do some manual style calculations such as... (not completely tested, but just as an idea)...
              SQL> ed
              Wrote file afiedt.buf
              
                1  with t as (select date '2012-06-28' as start_date, date '2013-01-31' as end_date from dual union all
                2             select date '2012-02-29', date '2012-06-30' from dual)
                3  --
                4  select start_date, end_date
                5        ,months_between(end_date,start_date) as mnth_bet
                6        ,case when to_number(to_char(end_date,'DD')) >= to_number(to_char(start_date,'DD')) then
                7           floor(months_between(end_date,start_date))
                8         else
                9           floor(months_between(end_date,start_date))-1
               10         end as mnth
               11        ,case when to_number(to_char(end_date,'DD')) >= to_number(to_char(start_date,'DD')) then
               12           to_number(to_char(end_date,'DD')) - to_number(to_char(start_date,'DD'))
               13         else
               14           (to_number(to_char(last_day(start_date),'DD'))-to_number(to_char(start_date,'DD')))+
               15           (to_number(to_char(start_date,'DD')))
               16         end as dys
               17* from t
              SQL> /
              
              START_DATE           END_DATE               MNTH_BET       MNTH        DYS
              -------------------- -------------------- ---------- ---------- ----------
              28-JUN-2012 00:00:00 31-JAN-2013 00:00:00 7.09677419          7          3
              29-FEB-2012 00:00:00 30-JUN-2012 00:00:00          4          4          1
              • 4. Re: How to get days and months when two dates are given
                BluShadow
                lee200 wrote:
                Try this:
                WITH t AS
                (SELECT TO_DATE('28-JUN-2012', 'DD-MON-YYYY') date1, TO_DATE('31-JAN-2013', 'DD-MON-YYYY') date2 FROM dual)
                SELECT TRUNC(MONTHS_BETWEEN(date2, date1)) months,
                date2 - ADD_MONTHS(date1, TRUNC(MONTHS_BETWEEN(date2, date1))) days
                FROM   t
                You've fallen for the months_between rounding issue...
                SQL> ed
                Wrote file afiedt.buf
                
                  1  with t as (select date '2012-06-28' as start_date, date '2013-01-31' as end_date from dual union all
                  2             select date '2012-02-29', date '2012-06-30' from dual)
                  3  --
                  4  SELECT TRUNC(MONTHS_BETWEEN(end_date, start_date)) months,
                  5         end_date - ADD_MONTHS(start_date, TRUNC(MONTHS_BETWEEN(end_date, start_date))) days
                  6* from t
                SQL> /
                
                    MONTHS       DAYS
                ---------- ----------
                         7          3
                         4          0
                Most people would say that there is 4 months and 1 day between 29th Feb 2012 and 30th June 2012. etc.

                Edited by: BluShadow on 14-Jan-2013 14:42

                I see the OP has been too quick to mark your answer as correct... ah well.
                • 5. Re: How to get days and months when two dates are given
                  Bawer
                  select TO_CHAR(diff, 'DD') - 1 as day
                        ,TO_CHAR(diff, 'MM') - 1 as month
                        ,TO_CHAR(diff, 'YYYY') - 1 as year
                  from
                  (    select dummy_date + ( TO_DATE(:a) - TO_DATE(:b) ) as diff from (
                        select to_Date('01.01.0001','DD.MM.YYYY')  as dummy_date from dual
                      )
                  )
                  • 6. Re: How to get days and months when two dates are given
                    BluShadow
                    Correction to my own solution...
                    SQL> ed
                    Wrote file afiedt.buf
                    
                      1  with t as (select date '2012-06-28' as start_date, date '2013-01-31' as end_date from dual union all
                      2             select date '2012-02-29', date '2012-06-30' from dual union all
                      3             select date '2012-02-29', date '2012-06-15' from dual
                      4            )
                      5  --
                      6  select start_date, end_date
                      7        ,months_between(end_date,start_date) as mnth_bet
                      8        ,floor(months_between(end_date,start_date)) as mnth
                      9        ,case when to_number(to_char(end_date,'DD')) >= to_number(to_char(start_date,'DD')) then
                     10           to_number(to_char(end_date,'DD')) - to_number(to_char(start_date,'DD'))
                     11         else
                     12           (to_number(to_char(last_day(start_date),'DD'))-to_number(to_char(start_date,'DD')))+
                     13           (to_number(to_char(end_date,'DD')))
                     14         end as dys
                     15* from t
                    SQL> /
                    
                    START_DATE           END_DATE               MNTH_BET       MNTH        DYS
                    -------------------- -------------------- ---------- ---------- ----------
                    28-JUN-2012 00:00:00 31-JAN-2013 00:00:00 7.09677419          7          3
                    29-FEB-2012 00:00:00 30-JUN-2012 00:00:00          4          4          1
                    29-FEB-2012 00:00:00 15-JUN-2012 00:00:00  3.5483871          3         15
                    • 7. Re: How to get days and months when two dates are given
                      BluShadow
                      Bawer wrote:
                      select TO_CHAR(diff, 'DD') - 1 as day
                      ,TO_CHAR(diff, 'MM') - 1 as month
                      ,TO_CHAR(diff, 'YYYY') - 1 as year
                      from
                      (    select dummy_date + ( TO_DATE(:a) - TO_DATE(:b) ) as diff from (
                      select to_Date('01.01.0001','DD.MM.YYYY')  as dummy_date from dual
                      )
                      )
                      Interesting idea... not quite sure it works though...
                      SQL> ed
                      Wrote file afiedt.buf
                      
                        1  with t as (select date '2012-06-28' as start_date, date '2013-01-31' as end_date from dual union all
                        2             select date '2012-02-29', date '2012-06-30' from dual union all
                        3             select date '2012-02-29', date '2012-06-15' from dual
                        4            )
                        5  --
                        6  select start_date, end_date
                        7        ,TO_CHAR(diff, 'YYYY') - 1 as year
                        8        ,TO_CHAR(diff, 'MM') - 1 as month
                        9        ,TO_CHAR(diff, 'DD') - 1 as day
                       10  from
                       11  (    select start_date, end_date, to_Date('01.01.0001','DD.MM.YYYY') + ( TO_DATE(end_date) - TO_DATE(start_date) ) as diff
                       12       from t
                       13* )
                      SQL> /
                      
                      START_DATE           END_DATE                   YEAR      MONTH        DAY
                      -------------------- -------------------- ---------- ---------- ----------
                      28-JUN-2012 00:00:00 31-JAN-2013 00:00:00          0          7          5
                      29-FEB-2012 00:00:00 30-JUN-2012 00:00:00          0          4          2
                      29-FEB-2012 00:00:00 15-JUN-2012 00:00:00          0          3         17
                      • 8. Re: How to get days and months when two dates are given
                        Frank Kulash
                        Hi,

                        Here's one way. It makes a first approximation, assuming that the difference will just be the difference in months and the difference in days. If the difference in days is negative, then it needs to make an adjustment, which is what the main query is for:
                        WITH     first_pass     AS
                        (
                             SELECT     start_dt
                             ,     end_dt
                             ,     MONTHS_BETWEEN ( TRUNC (end_dt,   'MONTH')
                                              , TRUNC (start_dt, 'MONTH')
                                              )                 AS raw_month_dif
                             ,     EXTRACT (DAY FROM end_dt) 
                                   - EXTRACT (DAY FROM start_dt)     AS raw_day_dif
                             FROM       table_x
                        )
                        SELECT       start_dt, end_dt
                        ,       CASE
                                   WHEN  raw_day_dif >= 0
                                   THEN  raw_month_dif
                                   ELSE  raw_month_dif - 1
                               END      AS month_dif
                        ,       CASE
                                   WHEN  raw_day_dif >= 0
                                   THEN  raw_day_dif
                                   ELSE  raw_day_dif + EXTRACT (DAY FROM LAST_DAY (start_dt))
                               END     AS day_dif
                        FROM       first_pass
                        ;
                        Depending on how you want to handle months of different lengths, you may want to change this some.

                        This works fine if end_dt is more that a year after start_dt; months_dif may be greater than 12 in that case.

                        Edited by: Frank Kulash on Jan 14, 2013 9:56 AM
                        • 9. Re: How to get days and months when two dates are given
                          877722
                          BluShadow wrote:
                          Correction to my own solution...
                          SQL> ed
                          Wrote file afiedt.buf
                          
                          1  with t as (select date '2012-06-28' as start_date, date '2013-01-31' as end_date from dual union all
                          2             select date '2012-02-29', date '2012-06-30' from dual union all
                          3             select date '2012-02-29', date '2012-06-15' from dual
                          4            )
                          5  --
                          6  select start_date, end_date
                          7        ,months_between(end_date,start_date) as mnth_bet
                          8        ,floor(months_between(end_date,start_date)) as mnth
                          9        ,case when to_number(to_char(end_date,'DD')) >= to_number(to_char(start_date,'DD')) then
                          10           to_number(to_char(end_date,'DD')) - to_number(to_char(start_date,'DD'))
                          11         else
                          12           (to_number(to_char(last_day(start_date),'DD'))-to_number(to_char(start_date,'DD')))+
                          13           (to_number(to_char(end_date,'DD')))
                          14         end as dys
                          15* from t
                          SQL> /
                          
                          START_DATE           END_DATE               MNTH_BET       MNTH        DYS
                          -------------------- -------------------- ---------- ---------- ----------
                          28-JUN-2012 00:00:00 31-JAN-2013 00:00:00 7.09677419          7          3
                          29-FEB-2012 00:00:00 30-JUN-2012 00:00:00          4          4          1
                          29-FEB-2012 00:00:00 15-JUN-2012 00:00:00  3.5483871          3         15
                          Thanks a lot,BluShadow for your answer and would you mind giving it for the year as well like 0 years 7 months 3 days?


                          Thanks again for your help

                          Edited by: 874719 on Jan 14, 2013 6:58 AM
                          • 10. Re: How to get days and months when two dates are given
                            BluShadow
                            Yep, looks good to me Frank...
                            SQL> ed
                            Wrote file afiedt.buf
                            
                              1  with t as (select date '2012-06-28' as start_dt, date '2013-01-31' as end_dt from dual union all
                              2             select date '2012-02-29', date '2012-06-30' from dual union all
                              3             select date '2012-02-29', date '2012-06-15' from dual
                              4            )
                              5  --
                              6  ,first_pass        AS
                              7  (
                              8     SELECT  start_dt
                              9     ,       end_dt
                             10     ,       MONTHS_BETWEEN ( TRUNC (end_dt,   'MONTH')
                             11                            , TRUNC (start_dt, 'MONTH')
                             12                            )                AS raw_month_dif
                             13     ,       EXTRACT (DAY FROM end_dt)
                             14           - EXTRACT (DAY FROM start_dt)     AS raw_day_dif
                             15     FROM    t
                             16  )
                             17  SELECT       start_dt, end_dt
                             18  ,    CASE
                             19           WHEN  raw_day_dif >= 0
                             20           THEN  raw_month_dif
                             21           ELSE  raw_month_dif - 1
                             22       END   AS month_dif
                             23  ,    CASE
                             24           WHEN  raw_day_dif >= 0
                             25           THEN  raw_day_dif
                             26           ELSE  raw_day_dif + EXTRACT (DAY FROM LAST_DAY (start_dt))
                             27       END   AS day_dif
                             28* FROM         first_pass
                            SQL> /
                            
                            START_DT             END_DT                MONTH_DIF    DAY_DIF
                            -------------------- -------------------- ---------- ----------
                            28-JUN-2012 00:00:00 31-JAN-2013 00:00:00          7          3
                            29-FEB-2012 00:00:00 30-JUN-2012 00:00:00          4          1
                            29-FEB-2012 00:00:00 15-JUN-2012 00:00:00          3         15
                            • 11. Re: How to get days and months when two dates are given
                              BluShadow
                              874719 wrote:
                              Thanks a lot,BluShadow for your answer and would you mind giving it for the year as well like 0 years 7 months 3 days?
                              Your original post said:
                              I dont need to take the year into account as the dates difference is always less than an year
                              Now you've got the idea, why not have a go yourself first, and then if you're still not getting it, post what you've tried so we can help you.
                              • 12. Re: How to get days and months when two dates are given
                                Bawer
                                BluShadow wrote:
                                Bawer wrote:
                                select TO_CHAR(diff, 'DD') - 1 as day
                                ,TO_CHAR(diff, 'MM') - 1 as month
                                ,TO_CHAR(diff, 'YYYY') - 1 as year
                                from
                                (    select dummy_date + ( TO_DATE(:a) - TO_DATE(:b) ) as diff from (
                                select to_Date('01.01.0001','DD.MM.YYYY')  as dummy_date from dual
                                )
                                )
                                Interesting idea... not quite sure it works though...
                                yes, my mistake, here is corrected version:
                                select TO_CHAR(diff, 'DD')  as day
                                      ,TO_CHAR(diff, 'MM')  as month
                                      ,TO_CHAR(diff, 'YYYY') - 1 as year
                                from
                                (    select  ( (dummy_date + ( TO_DATE('31.01.2013') - TO_DATE('28.06.2012')  ) ) - interval '1' day  )  - interval '1' month as diff from (
                                      select to_Date('01.01.0001','DD.MM.YYYY')  as dummy_date from dual
                                    )
                                );
                                • 13. Re: How to get days and months when two dates are given
                                  BluShadow
                                  BluShadow wrote:
                                  874719 wrote:
                                  Thanks a lot,BluShadow for your answer and would you mind giving it for the year as well like 0 years 7 months 3 days?
                                  Your original post said:
                                  I dont need to take the year into account as the dates difference is always less than an year
                                  Now you've got the idea, why not have a go yourself first, and then if you're still not getting it, post what you've tried so we can help you.
                                  Oh... go on... I'm in a good mood today...
                                  SQL> ed
                                  Wrote file afiedt.buf
                                  
                                    1  with t as (select date '2012-06-28' as start_date, date '2013-01-31' as end_date from dual union all
                                    2             select date '2012-02-29', date '2012-06-30' from dual union all
                                    3             select date '2010-02-28', date '2012-06-30' from dual union all
                                    4             select date '2012-02-29', date '2012-06-15' from dual
                                    5            )
                                    6  --
                                    7  select start_date, end_date
                                    8        ,months_between(end_date,start_date) as mnth_bet
                                    9        ,floor(months_between(end_date,start_date)/12) as yr
                                   10        ,floor(mod(months_between(end_date,start_date),12)) as mnth
                                   11        ,case when to_number(to_char(end_date,'DD')) >= to_number(to_char(start_date,'DD')) then
                                   12           to_number(to_char(end_date,'DD')) - to_number(to_char(start_date,'DD'))
                                   13         else
                                   14           (to_number(to_char(last_day(start_date),'DD'))-to_number(to_char(start_date,'DD')))+
                                   15           (to_number(to_char(end_date,'DD')))
                                   16         end as dys
                                   17* from t
                                  SQL> /
                                  
                                  START_DATE           END_DATE               MNTH_BET         YR       MNTH        DYS
                                  -------------------- -------------------- ---------- ---------- ---------- ----------
                                  28-JUN-2012 00:00:00 31-JAN-2013 00:00:00 7.09677419          0          7          3
                                  29-FEB-2012 00:00:00 30-JUN-2012 00:00:00          4          0          4          1
                                  28-FEB-2010 00:00:00 30-JUN-2012 00:00:00         28          2          4          2
                                  29-FEB-2012 00:00:00 15-JUN-2012 00:00:00  3.5483871          0          3         15
                                  • 14. Re: How to get days and months when two dates are given
                                    BluShadow
                                    Bawer wrote:
                                    yes, my mistake, here is corrected version:
                                    Maybe it's something I'm doing, but still I get results that look wrong with your solution...
                                    SQL> ed
                                    Wrote file afiedt.buf
                                    
                                      1  with t as (select date '2012-06-28' as start_date, date '2013-01-31' as end_date from dual union all
                                      2             select date '2012-02-29', date '2012-06-30' from dual union all
                                      3             select date '2012-02-29', date '2012-06-15' from dual
                                      4            )
                                      5  --
                                      6  select start_date, end_date
                                      7        ,TO_CHAR(diff, 'YYYY') - 1 as year
                                      8        ,TO_CHAR(diff, 'MM')  as month
                                      9        ,TO_CHAR(diff, 'DD')  as day
                                     10  from
                                     11  ( select  start_date, end_date
                                     12           ,( (dummy_date + ( end_date - start_date  ) ) - interval '1' day  )  - interval '1' month as diff
                                     13      from (
                                     14        select to_Date('01.01.0001','DD.MM.YYYY')  as dummy_date from dual
                                     15      ), t
                                     16* )
                                    SQL> /
                                    
                                    START_DATE           END_DATE                   YEAR MO DA
                                    -------------------- -------------------- ---------- -- --
                                    28-JUN-2012 00:00:00 31-JAN-2013 00:00:00          0 07 05
                                    29-FEB-2012 00:00:00 30-JUN-2012 00:00:00          0 04 02
                                    29-FEB-2012 00:00:00 15-JUN-2012 00:00:00          0 03 17
                                    1 2 Previous Next