ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND

Following error occurred during running of one query which having date calculation.

Error:
ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND

SELECT floor(sysdate-open_time) “Days” FROM v$containers;

Example:

SQL> SELECT floor(sysdate-open_time) FROM v$containers;
SELECT floor(sysdate-open_time) FROM v$containers
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND


Cause:
On checking the V$containers database the open_time columns has datatype timestamp and sysdate is date datatype

Solution:
we need to fixed this by using the CAST function as CAST(column_name as date).

Example:

SELECT name,floor(sysdate-cast(open_time as date)) "Days" FROM v$containers;


NAME Days
-------- ----------
CDB$ROOT 5
PDB$SEED 5
XEPDB1 5
PDB3 1