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

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.