This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Jan 15, 2013 3:12 AM by 877722 RSS

How to get days and months when two dates are given

877722 Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points