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.

Is Nobody Having answer to this? – Stanley Mungai Nov 01 '12 at 10:07 — Stanley Mungai, Nov 01 '12 at 10:07
Shouldn't it be 2 months and 24 days? – Noel Aug 14 '13 at 00:38 — Noel, Aug 14 '13 at 00:38

score 22 · Accepted Answer · edited Aug 15 '13 at 05:53

22

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

edited Aug 15 '13 at 05:53

ThinkJet

6,725
24
33

answered Aug 13 '13 at 16:00

jen

286
2
3

Please what does dt2 and dt1 represent I thought I was some dates? – Stanley Mungai Aug 14 '13 at 05:41
1

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. – Jon Heller Aug 15 '13 at 05:29
@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. – ThinkJet Aug 15 '13 at 06:07
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. – David Aldridge Aug 15 '13 at 07:15
Finally jen. This is What I was Looking for!!!!! Thanks It took one year and One Month :) – Stanley Mungai Aug 15 '13 at 08:46

Ravi K Thapliyal · Answer 2 · 2013-08-15T06:32:52.437

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).

@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. — Ravi K Thapliyal, Aug 14 '13 at 12:53
Multiplying by 30 or 31 is not a good idea. It will break for some dates, especially when February is involved. — Noel, Aug 15 '13 at 05:32
@Ramblin'Man I ran some more tests. I get what you're saying now. — Ravi K Thapliyal, Aug 15 '13 at 05:55
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. — vapcguy, Dec 06 '16 at 14:45

score 2 · Answer 3 · answered Aug 15 '13 at 07:13

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?

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. — Jon Heller, Aug 16 '13 at 19:21
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`. — vapcguy, Dec 06 '16 at 14:49

planben · Answer 4 · 2013-08-15T05:23:12.403

1

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;

edited Aug 15 '13 at 05:23

answered Aug 15 '13 at 04:53

planben

680
6
20

This is Also Correct :P – Stanley Mungai Aug 15 '13 at 08:52

score 1 · Answer 5 · answered Dec 08 '16 at 00:24

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.

score 0 · Answer 6 · answered Aug 14 '13 at 12:10

0

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);

answered Aug 14 '13 at 12:10

Harshit

560
1
5
15

3

Multiplying by 30 or 31 is not a good idea. It will break for some dates, especially when February is involved. – Noel Aug 15 '13 at 05:32
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. – vapcguy Dec 06 '16 at 15:32

score -2 · Answer 7 · edited Sep 17 '13 at 05:46

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;

Sami · Answer 8 · 2012-07-16T12:26:30.570

-3

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

edited Jul 16 '12 at 12:26

answered Jul 16 '12 at 09:34

Sami

8,168
9
66
99

DateDiff is in what Oracle table because It returns Invaid Identifier in dual>??? – Stanley Mungai Jul 16 '12 at 10:46
Yes. You are right I would provide you soon the oracle specific solution – Sami Jul 16 '12 at 11:49
If datediff does not work for you simply try date2-date1 instead of datediff(day,date2,date1) Whatever worked share with me please.. Thanks – Sami Jul 16 '12 at 12:28
http://www.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 – Sami Jul 16 '12 at 12:41
Please Understand the required Output if it is say 122 days, that should be 2months and 14 days – Stanley Mungai Apr 12 '13 at 12:55
`DateDiff` is MySQL and MS SQL, not for Oracle. – vapcguy Dec 06 '16 at 15:28

score -4 · Answer 9 · answered Aug 13 '13 at 21:23

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).

Oracle question, not a SQL Server question. – David Aldridge Aug 13 '13 at 21:27 — David Aldridge, Aug 13 '13 at 21:27

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

9 Answers9

Linked