1 2 Previous Next 21 Replies Latest reply: Jan 15, 2013 5:12 AM by 877722 Go to original post RSS
      • 15. Re: How to get days and months when two dates are given
        877722
        Thanks so nice of you... Thanks for your time and solutions,Mr.BluShadow.
        • 16. Re: How to get days and months when two dates are given
          Bawer
          I think, I got it :-)
          with t as (
                  select date '2012-06-28' as start_date, date '2013-01-31' as end_date from dual union all
                  select date '2012-02-29', date '2012-06-30' from dual union all
                  select date '2012-02-29', date '2012-06-15' from dual union all
                  select date '2012-02-29', date '2012-06-28' from dual
          )
          select TO_CHAR(diff, 'DD')  as day
                ,TO_CHAR(diff, 'MM') - 1 as month
                ,mnth_bet
                ,TO_CHAR(diff, 'YYYY') - 1 as year
          from
          (    select  ( (to_Date('01.01.0001','DD.MM.YYYY') + ( end_date - start_date ) )  - interval '1' day) as diff 
                , round(months_between(end_date,start_date),5) as mnth_bet
          from t
          )
          output:
          DAY MONTH MNTH_BET YEAR
          --- ----- -------- ----
          05      7  7,09677    0 
          02      4        4    0 
          17      3  3,54839    0 
          30      3  3,96774    0 
          your query:
          with t as (select date '2012-06-28' as start_date, date '2013-01-31' as end_date from dual union all
                  select date '2012-02-29', date '2012-06-30' from dual union all
                  select date '2012-02-29', date '2012-06-15' from dual union all
                  select date '2012-02-29', date '2012-06-28' from dual
                         )
              --
              select start_date, end_date
                    ,months_between(end_date,start_date) as mnth_bet
                    ,case when to_number(to_char(end_date,'DD')) >= to_number(to_char(start_date,'DD')) then
                       floor(months_between(end_date,start_date))
                     else
                       floor(months_between(end_date,start_date))-1
                    end as mnth
                   ,case when to_number(to_char(end_date,'DD')) >= to_number(to_char(start_date,'DD')) then
                      to_number(to_char(end_date,'DD')) - to_number(to_char(start_date,'DD'))
                    else
                      (to_number(to_char(last_day(start_date),'DD'))-to_number(to_char(start_date,'DD')))+
                      (to_number(to_char(start_date,'DD')))
                    end as dys
            from t
          returns:
          START_DATE END_DATE  MNTH_BET MNTH DYS
          ---------- --------- -------- ---- ---
          28.06.2012 31.01.201  7,09677    7   3 
          00:00:00   3 00:00:0                   
                     0                           
          
          29.02.2012 30.06.201        4    4   1 
          00:00:00   2 00:00:0                   
                     0                           
          
          29.02.2012 15.06.201  3,54839    2  29 
          00:00:00   2 00:00:0                   
                     0                           
          
          29.02.2012 28.06.201  3,96774    2  29 
          00:00:00   2 00:00:0                   
                     0                           
          I don't think too your query is completely correct.
          • 17. Re: How to get days and months when two dates are given
            BluShadow
            Bawer wrote:
            I think, I got it :-)
            with t as (
            select date '2012-06-28' as start_date, date '2013-01-31' as end_date from dual union all
            select date '2012-02-29', date '2012-06-30' from dual union all
            select date '2012-02-29', date '2012-06-15' from dual union all
            select date '2012-02-29', date '2012-06-28' from dual
            )
            .. cut..
            output:
            DAY MONTH MNTH_BET YEAR
            --- ----- -------- ----
            05 7 7,09677 0
            02 4 4 0
            17 3 3,54839 0
            30 3 3,96774 0
            Work it out in your head.
            for the first dates, the difference is 7 months and 3 days (28 to 31 = 3 days)
            for the second dates, the difference is 4 months and 1 day (29 to 30 = 1 day)
            for the third dates, the difference is a little more tricky, and this is where it's open to interpretation. 29th Feb is the last day of Feb, so we could say that there's only 15 days from the last day to the 15th... but like you've done could also be correct, as we could add 3 months to 29th Feb to take us to 29th May, and then it's 2 days to the end of May and 15 days to 15th June, giving us 17 days. All depends what the acual requirements are.

            Working out the days AND months between dates is one of those things that is a "it depends" because it really does depend on the requirements.
            Oracle's months_between function likes to treat the last day of each month as being the same so it can give whole months, but that can also give problems in itself, yet there are times when you want them to be the same.

            At the end of the day it's up to the OP to take the prinicples and work out which method suits them best... but certainly I would say there is 7 months and 3 days between 28/6/2012 and 31/01/2013, not 7 months and 5 days. ;)
            • 18. Re: How to get days and months when two dates are given
              Bawer
              BluShadow wrote:
              for the first dates, the difference is 7 months and 3 days (28 to 31 = 3 days)
              for the second dates, the difference is 4 months and 1 day (29 to 30 = 1 day)
              right, my query starts with first day of a dummy year, calculates the first 31 days as one month and next 28 days as one month.
              At the end of the day it's up to the OP to take the prinicples and work out which method suits them best...
              the decisive is definition of month calculation.
              but certainly I would say there is 7 months and 3 days between 28/6/2012 and 31/01/2013, not 7 months and 5 days. ;)
              is differently according to definition of calculation.
              I can say this is 7 months and 1 day ;-)
              • 19. Re: How to get days and months when two dates are given
                877722
                Dear Bawer and BluShadow,
                Based on my requirement, I should be considering "30 days" for each month.

                Just to provide you with the exact requirement so that you guys can come to a decisive conclusion.

                Thanks both of you.
                • 20. Re: How to get days and months when two dates are given
                  BluShadow
                  874719 wrote:
                  Dear Bawer and BluShadow,
                  Based on my requirement, I should be considering "30 days" for each month.
                  That wouldn't particular allow for accurate results in most people's interpretation of the issue.

                  Such a requirement would mean that e.g.

                  The difference between 31st January and 28th February (assuming non-leap year) is 0 months and 28 days.
                  The difference between 31st January and 1st March is 1 month and 1 day.

                  Thus you wouldn't have a difference that is exactly 1 month.

                  The calendar of the year is very changeable, especially with leap years, and with months having between 28 and 31 days each.

                  So just saying each month should be considered to have 30 days is ridiculous. That would also imply that a year only has 360 days, and the more years apart your dates are the more inaccurate your difference becomes.

                  e.g. these are the results where you have a fixed month of 30 days...
                  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 union all
                    4             select date '2010-01-01', date '2013-01-01' from dual
                    5            )
                    6  --
                    7  select start_date, end_date
                    8        ,floor((end_date-start_date)/360) as yrs
                    9        ,floor(mod(end_date-start_date,360)/30) as mnths
                   10        ,mod(end_date-start_date,30) as dys
                   11* from t
                  SQL> /
                  
                  START_DATE           END_DATE                    YRS      MNTHS        DYS
                  -------------------- -------------------- ---------- ---------- ----------
                  28-JUN-2012 00:00:00 31-JAN-2013 00:00:00          0          7          7
                  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
                  01-JAN-2010 00:00:00 01-JAN-2013 00:00:00          3          0         16
                  A difference of 3 years and 16 days between 1st Jan 2010 and 1st Jan 2013... really? Is that what you want?

                  So, back to you... YOU provide us with some example dates and YOU provide us with the output you expect to get from those dates, giving an explanantion as to how you came to that expected output from your example data.
                  • 21. Re: How to get days and months when two dates are given
                    877722
                    Dear Blushadow,

                    I totally agree with your point. But the thing is that the Client is calculating the Indemnity and they would want to consider the no of days in a month to be 30 that means they are fine with an year showing 360 days which helps them calculate and pay less.

                    I was just clearing the air when you were mentioning that the OP should decide on how exactly they want it.

                    Anyways thanks a ton for your time. You have provided insights into the months_between feature as well.

                    Thanks
                    1 2 Previous Next