Monday, September 17, 2012

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

It looks like you are trying to do maths (+, -) with TIMESTAMP.
TIMESTAMP doesn't like that. you should CAST the TIMESTAMP to DATE:

rather than
bla - blu (where bla and blu are TIMESTAMP)
do
CAST (bla as DATE) - CAST (blu as DATE)
and you will get a NUMBER (multiply it by 3600 * 24 and you will turn it into seconds)

BUT
you will lose the millisecond info

Here you have the definition of the TIMESTAMP

In alternative, you should rather EXTRACT each component of the DIFFERENCE (day, hours, minutes, seconds) and do the match with them:

as explained here

http://www.dbforums.com/oracle/1602071-difference-between-two-timestamps.html

CREATE OR REPLACE FUNCTION timestamp_diff
(
start_time_in TIMESTAMP
, end_time_in TIMESTAMP
)
RETURN NUMBER
AS
l_days NUMBER;
l_hours NUMBER;
l_minutes NUMBER;
l_seconds NUMBER;
l_milliseconds NUMBER;
BEGIN
SELECT extract(DAY FROM end_time_in-start_time_in)
, extract(HOUR FROM end_time_in-start_time_in)
, extract(MINUTE FROM end_time_in-start_time_in)
, extract(SECOND FROM end_time_in-start_time_in)
INTO l_days, l_hours, l_minutes, l_seconds
FROM dual;

l_milliseconds := l_seconds*1000 + l_minutes*60*1000 + l_hours*60*60*1000 + l_days*24*60*60*1000;
RETURN l_milliseconds;

END;



No comments: