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.
This question is related to
sql
oracle
date-arithmetic
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).
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;
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).
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?
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);
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
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.
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;
MsSql Syntax : DATEDIFF ( datepart , startdate , enddate )
Oracle: This will returns number of days
select
round(Second_date - First_date) as Diff_InDays,round ((Second_date - First_date) / (30),1) as Diff_InMonths,round ((Second_date - First_date) * (60*24),2) as TimeIn_Minitues
from
(
select
to_date('01/01/2012 01:30:00 PM','mm/dd/yyyy hh:mi:ss am') as First_date
,to_date('05/02/2012 01:35:00 PM','mm/dd/yyyy HH:MI:SS AM') as Second_date
from
dual
) result;
Source: Stackoverflow.com