Get the difference between two dates both In Months and days in sql

Question

I need to get the difference between two dates say if the difference is 84 days, I should probably have output as 2 months and 14 days, the code I have just gives the totals. Here is the code

SELECT Months_between(To_date('20120325', 'YYYYMMDD'),
       To_date('20120101', 'YYYYMMDD'))
       num_months,
       ( To_date('20120325', 'YYYYMMDD') - To_date('20120101', 'YYYYMMDD') )
       diff_in_days
FROM   dual; 

Output is:

NUM_MONTHS    DIFF_IN_DAYS
2.774193548       84

I need for example the output for this query to be either 2 months and 14 days at worst, otherwise I won't mind if I can have the exact days after the months figure because those days are not really 14 because all months do not have 30 days.

Muth

Is Nobody Having answer to this? – Muth

Shouldn't it be 2 months and 24 days? – Improvisator

Answer 1

select 
  dt1, dt2,
  trunc( months_between(dt2,dt1) ) mths, 
  dt2 - add_months( dt1, trunc(months_between(dt2,dt1)) ) days
from
(
    select date '2012-01-01' dt1, date '2012-03-25' dt2 from dual union all
    select date '2012-01-01' dt1, date '2013-01-01' dt2 from dual union all
    select date '2012-01-01' dt1, date '2012-01-01' dt2 from dual union all
    select date '2012-02-28' dt1, date '2012-03-01' dt2 from dual union all
    select date '2013-02-28' dt1, date '2013-03-01' dt2 from dual union all
    select date '2013-02-28' dt1, date '2013-04-01' dt2 from dual union all
    select trunc(sysdate-1)  dt1, sysdate               from dual
) sample_data

Results:

|                        DT1 |                       DT2 | MTHS |     DAYS |
----------------------------------------------------------------------------
|  January, 01 2012 00:00:00 |   March, 25 2012 00:00:00 |    2 |       24 |
|  January, 01 2012 00:00:00 | January, 01 2013 00:00:00 |   12 |        0 |
|  January, 01 2012 00:00:00 | January, 01 2012 00:00:00 |    0 |        0 |
| February, 28 2012 00:00:00 |   March, 01 2012 00:00:00 |    0 |        2 |
| February, 28 2013 00:00:00 |   March, 01 2013 00:00:00 |    0 |        1 |
| February, 28 2013 00:00:00 |   April, 01 2013 00:00:00 |    1 |        1 |
|   August, 14 2013 00:00:00 |  August, 15 2013 05:47:26 |    0 | 1.241273 |

Link to test: SQLFiddle

Tegular

Please what does dt2 and dt1 represent I thought I was some dates? – Muth

Hope you don't mind I added some sample data to your query. None of the other answers produce results as accurate. Most are not even remotely close. It's the results around February 28th that really show the difference. Assuming 30 or 31 days per month will not always work. I'm not sure if there is an official algorithm for counting months and days, but these results look the best to me. – Sever

@Stanley I joined jonearles and add two more examples for leap year and date with time plus link to site where anyone can test solution online. – Aseptic

Can you try the following date pairs and see if the answers make sense to you? 2013-01-28 and 2013-02-28, 2013-01-29 and 2013-02-28, 2013-01-30 and 2013-02-28, and 2013-01-31 and 2013-02-28. – Korikorie

Finally jen. This is What I was Looking for!!!!! Thanks It took one year and One Month :) – Muth

Answer 2

Updated for correctness. Originally answered by @jen.

with DATES as (
   select TO_DATE('20120101', 'YYYYMMDD') as Date1,
          TO_DATE('20120325', 'YYYYMMDD') as Date2
   from DUAL union all
   select TO_DATE('20120101', 'YYYYMMDD') as Date1,
          TO_DATE('20130101', 'YYYYMMDD') as Date2
   from DUAL union all
   select TO_DATE('20120101', 'YYYYMMDD') as Date1,
          TO_DATE('20120101', 'YYYYMMDD') as Date2
   from DUAL union all
   select TO_DATE('20130228', 'YYYYMMDD') as Date1,
          TO_DATE('20130301', 'YYYYMMDD') as Date2
   from DUAL union all
   select TO_DATE('20130228', 'YYYYMMDD') as Date1,
          TO_DATE('20130401', 'YYYYMMDD') as Date2
   from DUAL
), MONTHS_BTW as (
   select Date1, Date2,
          MONTHS_BETWEEN(Date2, Date1) as NumOfMonths
   from DATES
)
select TO_CHAR(Date1, 'MON DD YYYY') as Date_1,
       TO_CHAR(Date2, 'MON DD YYYY') as Date_2,
       NumOfMonths as Num_Of_Months,
       TRUNC(NumOfMonths) as "Month(s)",
       ADD_MONTHS(Date2, - TRUNC(NumOfMonths)) - Date1 as "Day(s)"
from MONTHS_BTW;

SQLFiddle Demo :

    +--------------+--------------+-----------------+-----------+--------+
    |   DATE_1     |   DATE_2     | NUM_OF_MONTHS   | MONTH(S)  | DAY(S) |
    +--------------+--------------+-----------------+-----------+--------+
    | JAN 01 2012  | MAR 25 2012  | 2.774193548387  |        2  |     24 |
    | JAN 01 2012  | JAN 01 2013  | 12              |       12  |      0 |
    | JAN 01 2012  | JAN 01 2012  | 0               |        0  |      0 |
    | FEB 28 2013  | MAR 01 2013  | 0.129032258065  |        0  |      1 |
    | FEB 28 2013  | APR 01 2013  | 1.129032258065  |        1  |      1 |
    +--------------+--------------+-----------------+-----------+--------+

Notice, how for the last two dates, Oracle reports the decimal part of months (which gives days) incorrectly. 0.1290 corresponds to exactly 4 days with Oracle considering 31 days in a month (for both March and April).

Schiffman

Not very accurate. It should have returned 24 days. – Improvisator

@Ramblin'Man Thanks for the heads up. Apparently, using 31 as "days in a month" seems to have fixed it. I actually thought that both 30 and 31 would sometimes return the exact and sometimes off by 1 number of days (depending on what months were chosen) but, the round takes care of it. Have a look again if you like. – Schiffman

Multiplying by 30 or 31 is not a good idea. It will break for some dates, especially when February is involved. – Improvisator

@Ramblin'Man I ran some more tests. I get what you're saying now. – Schiffman

Use 30.41667, which is (365/12). This is the average number of days in a month, for a year. I did find when I put (365/12) in a SQLite query that it made it 30, instead (it somehow floored my query, though I don't know how), so I had to plug in 30.41667 before things started being accurate, so watch out for this in case it crops up in Oracle, too, especially since you would have to FLOOR (or TRUNC, as I see above) your MONTHS_BETWEEN to subtract them away to get your decimal, and therefore, days portion, to multiply this by. – Adorne

Answer 3

I think that your question is not defined well enough, for the following reason.

Answers relying on months_between have to deal with the following issue: that the function reports exactly one month between 2013-02-28 and 2013-03-31, and between 2013-01-28 and 2013-02-28, and between 2013-01-31 and 2013-02-28 (I suspect that some answerers have not used these functions in practice, or are now going to have to review some production code!)

This is documented behaviour, in which dates that are both the last in their respective months or which fall on the same day of the month are judged to be an integer number of months apart.

So, you get the same result of "1" when comparing 2013-02-28 with 2013-01-28 or with 2013-01-31, but comparing it with 2013-01-29 or 2013-01-30 gives 0.967741935484 and 0.935483870968 respectively -- so as one date approaches the other the difference reported by this function can increase.

If this is not an acceptable situation then you'll have to write a more complex function, or just rely on a calculation that assumes 30 (for example) days per month. In the latter case, how will you deal with 2013-02-28 and 2013-03-31?

Korikorie

You bring up some excellent points. This is a simple, common question with no perfect answer. Whichever solution is used, it should probably include a disclaimer. I'm still going to award the bounty to the accepted answer since it feels right to me. But that may only be because I've used months_between too much. – Sever

Yes, as Jon Heller says, good point of how 2-28 to 3-31 is 1, instead of 2-28 to 3-28 = 1. That's why, when you get down to days, you should really count days, not MONTHS_BETWEEN. – Adorne

Answer 4

is this what you've ment ?

select trunc(months_between(To_date('20120325', 'YYYYMMDD'),to_date('20120101','YYYYMMDD'))) months,
             round(To_date('20120325', 'YYYYMMDD')-add_months(to_date('20120101','YYYYMMDD'),
                           trunc(months_between(To_date('20120325', 'YYYYMMDD'),to_date('20120101','YYYYMMDD'))))) days
        from dual;

Scrapbook

This is Also Correct :P – Muth

Answer 5

Here I'm just doing the difference between today, and a CREATED_DATE DATE field in a table, which obviously is a date in the past:

SELECT  
((FLOOR(ABS(MONTHS_BETWEEN(CREATED_DATE, SYSDATE))) / 12) * 12) || ' months, '  AS MONTHS,
-- we take total days - years(as days) - months(as days) to get remaining days
FLOOR((SYSDATE - CREATED_DATE) -      -- total days
(FLOOR((SYSDATE - CREATED_DATE)/365)*12)*(365/12) -      -- years, as days
-- this is total months - years (as months), to get number of months, 
-- then multiplied by 30.416667 to get months as days (and remove it from total days)
FLOOR(((SYSDATE - CREATED_DATE)/365)*12 - (FLOOR((SYSDATE - CREATED_DATE)/365)*12)) * (365/12))
|| ' days ' AS DAYS 
FROM MyTable

I use (365/12), or 30.416667, as my conversion factor because I'm using total days and removing years and months (as days) to get the remainder number of days. It was good enough for my purposes, anyway.

Adorne

Answer 6

The solution I post will consider a month with 30 days

  select CONCAT (CONCAT (num_months,' MONTHS '), CONCAT ((days-(num_months)*30),' DAYS '))
  from ( 
  SELECT floor(Months_between(To_date('20120325', 'YYYYMMDD'),
   To_date('20120101', 'YYYYMMDD')))
   num_months,
   ( To_date('20120325', 'YYYYMMDD') - To_date('20120101', 'YYYYMMDD') )
   days
  FROM   dual);

Forequarter

Multiplying by 30 or 31 is not a good idea. It will break for some dates, especially when February is involved. – Improvisator

I multiply by (365/12), or 30.41667, but the way to use that isn't to directly find the actual days using that resulting figure, like above. You can use it to covert the MONTHS_BETWEEN to days, which gives the total days, then subtract the amount of time (in days) that are months, to find the actual remaining days. – Adorne

Answer 7

Find out Year - Month- Day between two Days in Orale Sql

---

select 
trunc(trunc(months_between(To_date('20120101', 'YYYYMMDD'),to_date('19910228','YYYYMMDD')))/12) years ,
trunc(months_between(To_date('20120101', 'YYYYMMDD'),to_date('19910228','YYYYMMDD'))) 
-
(trunc(trunc(months_between(To_date('20120101', 'YYYYMMDD'),to_date('19910228','YYYYMMDD')))/12))*12
months,
             round(To_date('20120101', 'YYYYMMDD')-add_months(to_date('19910228','YYYYMMDD'),
                           trunc(months_between(To_date('20120101', 'YYYYMMDD'),to_date('19910228','YYYYMMDD'))))) days
        from dual;

Brainwashing

Answer 8

SELECT   (MONTHS_BETWEEN(date2,date1) +  (datediff(day,date2,date1))/30) as num_months,
datediff(day,date2,date1) as diff_in_days  FROM  dual;

// You should replace date2 with TO_DATE('2012/03/25', 'YYYY/MM/DD')
// You should replace date1 with TO_DATE('2012/01/01', 'YYYY/MM/DD')
// To get you results

Swanger

DateDiff is in what Oracle table because It returns Invaid Identifier in dual>??? – Muth

Yes. You are right I would provide you soon the oracle specific solution – Swanger

If datediff does not work for you simply try date2-date1 instead of datediff(day,date2,date1) Whatever worked share with me please.. Thanks – Swanger

w3schools.com/sql/func_datediff_mysql.asp Actually for the time being i have not oracle engine otherwise i think simple sql(language) function should work at oracle as well – Swanger

Please Understand the required Output if it is say 122 days, that should be 2months and 14 days – Muth

DateDiff is MySQL and MS SQL, not for Oracle. – Adorne

Answer 9

See the query below (assumed @dt1 >= @dt2);

Declare @dt1 datetime = '2013-7-3'
Declare @dt2 datetime = '2013-5-2'

select abs(DATEDIFF(DD, @dt2, @dt1)) Days,
case when @dt1 >= @dt2
    then case when DAY(@dt2)<=DAY(@dt1)
        then Convert(varchar, DATEDIFF(MONTH, @dt2, @dt1)) + CONVERT(varchar, ' Month(s) ') + Convert(varchar, DAY(@dt1)-DAY(@dt2)) + CONVERT(varchar, 'Day(s).')
        else Convert(varchar, DATEDIFF(MONTH, @dt2, @dt1)-1) + CONVERT(varchar, ' Month(s) ') + convert(varchar, abs(DATEDIFF(DD, @dt1, DateAdd(Month, -1, @dt1))) - (DAY(@dt2)-DAY(@dt1))) + CONVERT(varchar, 'Day(s).')
    end
    else 'See asumption: @dt1 must be >= @dt2'
end In_Months_Days

Returns:

Days | In_Months_Days

62   |   2 Month(s) 1Day(s).

Ferrick

Oracle question, not a SQL Server question. – Korikorie