CODEWITHSUNDEEP
sqloracle-databasedate-arithmetic
Stanley Mungai
Stanley Mungai

Reputation: 4150

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

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.

Upvotes: 15

Views: 118871

Answers (9)

vapcguy
vapcguy

Reputation: 7537

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.

Upvotes: 1

zico
zico

Reputation: 1

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;

Upvotes: -2

David Aldridge
David Aldridge

Reputation: 52346

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?

Upvotes: 2

Ravi K Thapliyal
Ravi K Thapliyal

Reputation: 51711

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

Upvotes: 2

jen
jen

Reputation: 286

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

Upvotes: 22

planben
planben

Reputation: 700

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;

Upvotes: 1

Harshit
Harshit

Reputation: 560

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

Upvotes: 0

Hassan Farid
Hassan Farid

Reputation: 113

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

Upvotes: -4

Sami
Sami

Reputation: 8419

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

Upvotes: -3

Related Questions