9 Replies Latest reply: Mar 4, 2010 6:49 AM by BluShadow RSS

    Exact Month & Days between 2 dates

    652211
      hello Seniors,

      i am using Oracle Database 10g Release 10.2.0.1. with Form 6i

      i want to find difference between SYSDATE and the user entered date field in the format example;
      select months_between(sysdate,to_date('15012010','ddmmyyyy')) ddiff from dual;
      
           DDIFF
      ----------
      1.66275911
      
      but i want the query to separate "mmnth" and "ddays" into numeric columns and display like this ;
      
      mmnth   ddays
         1       17
        • 1. Re: Exact Month & Days between 2 dates
          730428
          SQL> select trunc(months_between(sysdate,to_date('15012010','ddmmyyyy'))) months,
            2         round(sysdate-add_months(to_date('15012010','ddmmyyyy'),
            3                       trunc(months_between(sysdate,to_date('15012010','ddmmyyyy'))))) days
            4    from dual;
          
              MONTHS       DAYS
          ---------- ----------
                   1         17
          Max
          http://oracleitalia.wordpress.com
          • 2. Re: Exact Month & Days between 2 dates
            ravikumar.sv
            with tmp as
            (
              select to_date('15012010','ddmmyyyy') dt from dual
            ) 
             SELECT floor(months_between(sysdate,dt)) months,
              floor(sysdate-add_months(dt,floor(months_between(sysdate,dt)))) days
               FROM tmp
            untested

            Ravi Kumar
            • 3. Re: Exact Month & Days between 2 dates
              652211
              Thanks Mr. Massimo,

              but how come when i execute your solution, so till today it should be 17 dys.
              SCOTT@orcl> select trunc(months_between(sysdate,to_date('15012010','ddmmyyyy'))) months,
                2           round(sysdate-add_months(to_date('15012010','ddmmyyyy'),
                3                         trunc(months_between(sysdate,to_date('15012010','ddmmyyyy'))))) days
                4      from dual;
              
                  MONTHS       DAYS
              ---------- ----------
                       1         18
              from 15/01/2010 to 14/2/2010 = 1 month and
              from 15/02/2010 to 03/03/2010 = 17 dys (including 15/1/2010 and 03/03/2010)

              any suggestion..?
              TYVM
              • 4. Re: Exact Month & Days between 2 dates
                652211
                -- Duplicate

                Edited by: user649208 on Mar 4, 2010 1:34 AM
                • 5. Re: Exact Month & Days between 2 dates
                  674955
                  Try this - Replacethe Round in second line with FLOOR

                  >
                  select trunc(months_between(sysdate,to_date('15012010','ddmmyyyy'))) months,
                  floor(sysdate-add_months(to_date('15012010','ddmmyyyy'),
                  trunc(months_between(sysdate,to_date('15012010','ddmmyyyy'))))) days
                  from dual;
                  Edited by: Prasath on Mar 4, 2010 1:38 AM
                  • 6. Re: Exact Month & Days between 2 dates
                    730428
                    how come when i execute your solution, so till today it should be 17 dys.
                    It depends on the time part of sysdate.

                    The same query returns 17 days here in CET timezone because time is before 12AM.

                    use
                    select trunc(months_between(sysdate,to_date('15012010','ddmmyyyy'))) months,
                           trunc(sysdate-add_months(to_date('15012010','ddmmyyyy'),
                                         trunc(months_between(sysdate,to_date('15012010','ddmmyyyy'))))) days
                      from dual;
                    or
                    select trunc(months_between(sysdate,to_date('15012010','ddmmyyyy'))) months,
                           trunc(sysdate)-add_months(to_date('15012010','ddmmyyyy'),
                                         trunc(months_between(trunc(sysdate),to_date('15012010','ddmmyyyy')))) days
                      from dual;
                    Max
                    http://oracleitalia.wordpress.com
                    • 7. Re: Exact Month & Days between 2 dates
                      MichaelS
                      from 15/01/2010 to 14/2/2010 = 1 month and
                      when oracle adds a months it'll be
                      SQL> select add_months (date '2010-01-15', 1) from dual
                      
                      ADD_MONTHS(DATE'2010-01-15',1)
                      ------------------------------
                      15.02.2010                    
                      1 row selected.
                      Therefore
                      SQL> with t as (
                        select months_between (sysdate, date '2010-01-15') df from dual
                      )
                      --
                      --
                      select trunc (df) months,
                             round( (df - trunc (df)) * (sysdate - add_months (sysdate, -1)),4) days
                      from t
                      
                                    MONTHS                 DAYS
                      -------------------- --------------------
                                         1              18,4791
                      1 row selected.
                      If you want 1 day less than one month just subtract 1 day.
                      • 8. Re: Exact Month & Days between 2 dates
                        652211
                        Thank a lot, Massimo, Ravikumar, Prasath.
                        • 9. Re: Exact Month & Days between 2 dates
                          BluShadow
                          user649208 wrote:
                          from 15/01/2010 to 14/2/2010 = 1 month and
                          from 15/02/2010 to 03/03/2010 = 17 dys (including 15/1/2010 and 03/03/2010)

                          any suggestion..?
                          TYVM
                          Month and day logic can't be accurate...

                          Have you thought how you're going to handle it when the start date is something > 28th January?

                          e.g.

                          let's start with 28th January...

                          from 28/01/2010 00:00:00 to 28/02/2010 00:00:00 = 1 month and
                          from 28/02/2010 00:00:00 to 04/03/2010 00:00:00 = 4 days

                          Answer: 1 month 4 days

                          now let's start with 29th January...

                          from 29/01/2010 00:00:00 to 28/02/2010 00:00:00 = 1 month and
                          from 28/02/2010 00:00:00 to 04/03/2010 00:00:00 = 4 days

                          Answer: 1 month 4 days

                          Oracle will treat 28th February as being 1 month after 29th January:
                          SQL> select add_months(date '2010-01-29', 1) from dual;
                          
                          ADD_MONTHS(DATE'201
                          -------------------
                          28/02/2010 00:00:00
                          ... as it can't assume there are a fixed number of days in a month.

                          You'd also get the same for 30th and 31st January.

                          Just something you need to be aware of. ;)