[SOLVED] Oracle difference between to dates in days without considering time

Oracle difference between to dates in days without considering time

How to get days count from CURRENT_TIMESTAMP without considering time (hours,minutes,seconds). for eg. CURRENT_TIMESTAMP is 24-JUN-21 00.01.01 AM and EVENT_TIMESTAMP is 23-JUN-21 07.00.00 PM then it should return 1 day difference.

Below query returning me 0 days for above values, because time difference is less than 24 hours.

SELECT EXTRACT(DAY FROM (CURRENT_TIMESTAMP - EVENT_TIMESTAMP)) FROM TABLE;

Below query returning me 1 days difference till 7PM and it start showing 2 days difference after 7PM on same date.

SELECT CEIL(SYSDATE - CAST(EVENT_TIMESTAMP AS DATE)) FROM TABLE;

Thanks.

Solution

If I understood you correctly, you should just TRUNCate those values:

SQL> select   trunc(to_date('24.06.2021 00:01:01', 'dd.mm.yyyy hh24:mi:ss'))
  2         - trunc(to_date('23.06.2021 07:00:00', 'dd.mm.yyyy hh24:mi:ss')) result
  3  from dual;

    RESULT
----------
         1

SQL>

TRUNC will "remove" time component (actually, set it to midnight) and you'd subtract only dates. Difference of two DATE datatype values in Oracle represents number of days between them so ... maybe that's what you're looking for.