Skip to Main Content
  • Questions

  • Difference Between Two Dates With Time

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Amarnath.

Asked: August 02, 2016 - 6:12 am UTC

Last updated: August 08, 2016 - 12:49 pm UTC

Version: 10g

Viewed 10K+ times! This question is

You Asked

Sir,
There are two input dates with format 'DD/MM/YYYY HH:MI:SS'
How can I get difference between these two dates like

Input
----------------------------------------------
'15/11/2015 11:19:58'
'14/10/2014 10:18:57'
----------------------------------------------
Output should be
'01/01/01 01:01:01'

Please help me sir,
thank you...

and Chris said...

To return the difference between two datetimes as a datetime itself doesn't make sense!

For example, what's the difference between 1 Jan and 31 Mar as a date? 2 months and 30 days? But 30 Feb isn't a real date!

And what if it's a leap year? Should it be 2 months and 31 days? A month isn't a fixed amount of time.

What you could do is return the difference as two intervals. One year to month and one day to second.

Calculate the year to month interval as the floor of the months between the dates using months_between. Then find the days by adding this number of months to the start date, then subtracting this calculated date from the end date.

For example:

select numtoyminterval(floor(months_between(en, st)), 'month') year_months, 
       numtodsinterval(
         en - add_months(st, floor(months_between(en, st))), 
         'day'
       ) day_seconds
from (
select 
to_date('15/11/2015 11:19:58', 'dd/mm/yyyy hh24:mi:ss') en, 
to_date('14/10/2014 10:18:57', 'dd/mm/yyyy hh24:mi:ss') st
from dual
);

YEAR_MONTHS  DAY_SECONDS          
+01-01       +01 01:01:01.000000

Just remember you can't combine these together to form a date:

select numtoyminterval(floor(months_between(en, st)), 'month') year_months, 
       numtodsinterval(
         en - add_months(st, floor(months_between(en, st))), 
         'day'
       ) day_seconds
from (
select 
to_date('31/03/2016 11:19:58', 'dd/mm/yyyy hh24:mi:ss') en, 
to_date('01/01/2016 10:18:57', 'dd/mm/yyyy hh24:mi:ss') st
from dual
);

YEAR_MONTHS  DAY_SECONDS          
+00-02       +30 01:01:01.000000  

select to_date(lpad(extract(year from year_months), 2, '0') || 
    lpad(extract(month from year_months), 2, '0') || 
    lpad(extract(day from day_seconds), 2, '0') ||
    lpad(extract(hour from day_seconds), 2, '0') ||
    lpad(extract(minute from day_seconds), 2, '0') || 
    lpad(extract(second from day_seconds), 2, '0'), 'yymmddhh24miss')
from (
select numtoyminterval(floor(months_between(en, st)), 'month') year_months, 
       numtodsinterval(
         en - add_months(st, floor(months_between(en, st))), 
         'day'
       ) day_seconds
from (
select 
to_date('31/03/2016 11:19:58', 'dd/mm/yyyy hh24:mi:ss') en, 
to_date('01/01/2016 10:18:57', 'dd/mm/yyyy hh24:mi:ss') st
from dual
)
);

SQL Error: ORA-01839: date not valid for month specified

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, August 03, 2016 - 12:58 pm UTC

Sounds like the OP wants to express the difference as a Julian date
Chris Saxon
August 03, 2016 - 1:07 pm UTC

Like I said, expressing the difference between two dates as a (Julian) date is impossible in the general case. Some differences convert to values that are not a date!

Does this helps ?

Rajeshwaran, Jeyabal, August 03, 2016 - 1:25 pm UTC 

demo@ORA12C> select to_date('15/11/2015 11:19:58','dd/mm/yyyy hh24:mi:ss') end_date ,
  2     to_date('14/10/2014 10:18:57','dd/mm/yyyy hh24:mi:ss') start_date
  3  from dual ;

END_DATE             START_DATE
-------------------- --------------------
15-NOV-2015 11:19:58 14-OCT-2014 10:18:57

1 row selected.

demo@ORA12C> select end_date,start_date,
  2      trunc(months_between(end_date,start_date)/12) as yrs ,
  3      trunc(mod( months_between(end_date,start_date) ,12)) as mnts,
  4      trunc(end_date - add_months( start_date, months_between(end_date,start_date))) as dys,
  5      trunc(24*mod(end_date - start_date,1)) as hrs,
  6      trunc( mod(mod(end_date - start_date,1)*24,1)*60 ) as mins ,
  7      mod(mod(mod(end_date - start_date,1)*24,1)*60,1)*60 as secs
  8  from ( select to_date('15/11/2015 11:19:58','dd/mm/yyyy hh24:mi:ss') end_date ,
  9     to_date('14/10/2014 10:18:57','dd/mm/yyyy hh24:mi:ss') start_date
 10  from dual );

END_DATE             START_DATE                  YRS       MNTS        DYS        HRS       MINS       SECS
-------------------- -------------------- ---------- ---------- ---------- ---------- ---------- ----------
15-NOV-2015 11:19:58 14-OCT-2014 10:18:57          1          1          1          1          1       1

1 row selected.

demo@ORA12C> select to_date( to_char(yrs,'fm0000') ||
  2                             to_char(mnts,'fm00') ||
  3                             to_char(dys,'fm00') ||
  4                             to_char(hrs,'fm00')  ||
  5                             to_char(mins,'fm00') ||
  6                             to_char(secs,'fm00') ,'YYYYMMDDHH24MISS')
  7  from (
  8  select end_date,start_date,
  9      trunc(months_between(end_date,start_date)/12) as yrs ,
 10      trunc(mod( months_between(end_date,start_date) ,12)) as mnts,
 11      trunc(end_date - add_months( start_date, months_between(end_date,start_date))) as dys,
 12      trunc(24*mod(end_date - start_date,1)) as hrs,
 13      trunc( mod(mod(end_date - start_date,1)*24,1)*60 ) as mins ,
 14      mod(mod(mod(end_date - start_date,1)*24,1)*60,1)*60 as secs
 15  from ( select to_date('15/11/2015 11:19:58','dd/mm/yyyy hh24:mi:ss') end_date ,
 16     to_date('14/10/2014 10:18:57','dd/mm/yyyy hh24:mi:ss') start_date
 17  from dual )
 18     );

TO_DATE(TO_CHAR(YRS,
--------------------
01-JAN-0001 01:01:01

1 row selected.

demo@ORA12C>

Care should be taken

Rajeshwaran, Jeyabal, August 03, 2016 - 1:34 pm UTC

Like Chirs, said - Care should be taken for julian date preparation,

demo@ORA12C> select to_date('31/03/2016 11:19:58', 'dd/mm/yyyy hh24:mi:ss') end_date ,
  2     to_date('01/01/2016 10:18:57', 'dd/mm/yyyy hh24:mi:ss') start_date
  3  from dual ;

END_DATE             START_DATE
-------------------- --------------------
31-MAR-2016 11:19:58 01-JAN-2016 10:18:57

1 row selected.

demo@ORA12C> select end_date,start_date,
  2      trunc(months_between(end_date,start_date)/12) as yrs ,
  3      trunc(mod( months_between(end_date,start_date) ,12)) as mnts,
  4      trunc(end_date - add_months( start_date, months_between(end_date,start_date))) as dys,
  5      trunc(24*mod(end_date - start_date,1)) as hrs,
  6      trunc( mod(mod(end_date - start_date,1)*24,1)*60 ) as mins ,
  7      mod(mod(mod(end_date - start_date,1)*24,1)*60,1)*60 as secs
  8  from ( select to_date('31/03/2016 11:19:58', 'dd/mm/yyyy hh24:mi:ss') end_date ,
  9     to_date('01/01/2016 10:18:57', 'dd/mm/yyyy hh24:mi:ss') start_date
 10  from dual );

END_DATE             START_DATE                  YRS       MNTS        DYS        HRS       MINS       SECS
-------------------- -------------------- ---------- ---------- ---------- ---------- ---------- ----------
31-MAR-2016 11:19:58 01-JAN-2016 10:18:57          0          2         30          1          1       1

1 row selected.

demo@ORA12C> select to_date( to_char(yrs,'fm0000') ||
  2                             to_char(mnts,'fm00') ||
  3                             to_char(dys,'fm00') ||
  4                             to_char(hrs,'fm00')  ||
  5                             to_char(mins,'fm00') ||
  6                             to_char(secs,'fm00') ,'YYYYMMDDHH24MISS')
  7  from (
  8  select end_date,start_date,
  9      trunc(months_between(end_date,start_date)/12) as yrs ,
 10      trunc(mod( months_between(end_date,start_date) ,12)) as mnts,
 11      trunc(end_date - add_months( start_date, months_between(end_date,start_date))) as dys,
 12      trunc(24*mod(end_date - start_date,1)) as hrs,
 13      trunc( mod(mod(end_date - start_date,1)*24,1)*60 ) as mins ,
 14      mod(mod(mod(end_date - start_date,1)*24,1)*60,1)*60 as secs
 15  from ( select to_date('31/03/2016 11:19:58', 'dd/mm/yyyy hh24:mi:ss') end_date ,
 16     to_date('01/01/2016 10:18:57', 'dd/mm/yyyy hh24:mi:ss') start_date
 17  from dual )
 18     );
                                to_char(mins,'fm00') ||
                                                      *
ERROR at line 5:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0


demo@ORA12C>


So Given two dates as inputs, stick with getting the difference in terms of Years, Months, Days, Hours, Mins, Sec and dont put every thing back into a Date format - that may not work for all cases ( once such example is above )

Regarding solution

Amarnath Sharma, August 05, 2016 - 5:18 am UTC

Thank you for your valuable time.
Sir, I had try to solve this query
after submitting question to you,

and I wrote this query to get result;
this query runs and give result.

SELECT TRUNC(ABC/1) DAYS,
TRUNC((ABC-(TRUNC(ABC/1)))*24) HOURS,
CEIL(((ABC-(TRUNC(ABC/1)))*24-
TRUNC((ABC-(TRUNC(ABC/1)))*24))*60) MINUTES

FROM(SELECT TO_date('15/11/2015 11:19:58 AM', 'dd/mm/yyyy hh:mi:ss AM') - TO_date('11/11/2015 01:00:00 AM', 'dd/mm/yyyy hh:mi:ss AM') ABC FROM DUAL);


Sir plzz suggest me,
can i use this query to get result
Connor McDonald
August 08, 2016 - 12:49 pm UTC

I'm not sure why you've felt the need to divide abc by 1. It has no effect!

Rounding errors also mean this won't always give you the answer you expect. 

select trunc ( abc ) days, 
    trunc ( ( abc - ( trunc ( abc ) ) ) * 24 ) hours, 
    ceil ( 
      ( ( abc - ( trunc ( abc ) ) ) * 24 - 
          trunc ( ( abc - ( trunc ( abc ) ) ) * 24 ) 
      ) * 60 
    ) minutes
from
  (select to_date ( '15/11/2015 11:19:00 AM', 'dd/mm/yyyy hh:mi:ss AM' ) - 
          to_date ( '11/11/2015 01:21:00 AM', 'dd/mm/yyyy hh:mi:ss AM' ) abc
  from dual
  );

DAYS  HOURS  MINUTES  
4     9      59


01:21 -> 11:19 is 58 minutes, not 59!

So what's the correct answer?

It depends what your requirement is for displaying part minutes - rounding up, down or to the nearest minute?

The following rounds to the nearest minute:

select trunc ( abc ) days, 
    trunc ( ( abc - ( trunc ( abc ) ) ) * 24 ) hours, 
    mod(round((abc - ( trunc ( abc ))) * 1440), 60) minutes
from
  (select to_date ( '15/11/2015 11:19:00 AM', 'dd/mm/yyyy hh:mi:ss AM' ) - 
          to_date ( '11/11/2015 01:21:00 AM', 'dd/mm/yyyy hh:mi:ss AM' ) abc
  from dual
  );

DAYS  HOURS  MINUTES  
4     9      58

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.