Subtract dates and only show integer in SQL

Question

I'm trying to find the days difference between two dates, and when I write: 'select date_trunc('day',timetable.time) - date_released as no_days', I get a result that shows something like: "0 years 0 mons 3 days 16 hours 38 mins 28.00 secs". I only want to see '3' or '3 days'. Is there a way to eliminate years, mons, hours, mins, secs? I"m running this query in Mode Analytics, if that changes anything...

Answer 1

In T-SQL for Sql Server the standard datediff functions is easiest.

Try this: SELECT DATEDIFF(DAY, '01/01/2009', GETDATE())

That return the number of days between 01/01/2009 and today.

If it is Oracle, the DateDiff syntax is about the same:

DATEDIFF(expr1,expr2)

DATEDIFF() returns expr1 − expr2 expressed as a value in days from one date to the other.

HTH, Sean

Answer 2

In Oracle, floor is your friend.

select sysdate - {ts '2017-08-01 15:30:00'} c
from dual

returns 216.921412037037037037037037037037037037

while

select floor(sysdate - {ts '2017-08-01 15:30:00'}) c
from dual

returns 216

Answer 3

As far as I can tell, in Oracle difference of two DATE values results in number of days, so - just subtract them and remove everything behind the decimal point (which would be the TRUNC). For example:

SQL> select
  2    sysdate,
  3    sysdate - to_date('01.03.2018 21:00', 'dd.mm.yyyy hh24:mi') diff,
  4    trunc(sysdate - to_date('01.03.2018 21:00', 'dd.mm.yyyy hh24:mi')) result
  5  from dual;

SYSDATE                   DIFF     RESULT
------------------- ---------- ----------
06.03.2018 21:20:35 5,01429398          5

SQL>