ORA-00932: Inconsistent Datatypes: Expected Number Got Interval Day to Second Error While Updating a SQL
Below is the SQL.
Begin-SQL
UPDATE PS_TL_RPTD_TIME A
SET A.PUNCH_END = TO_DATE($Date_Time_End,'YYYY-MM-DD HH24:MI:SS'),
A.TL_QUANTITY = ROUND((TO_DATE($Date_Time_End,'YYYY-MM-DD HH24:MI:SS') - A.PUNCH_DTTM)*24,5)
WHERE A.EMPLID = $Employee_ID
AND A.EMPL_RCD = #Employee_Record
AND A.DUR = $Shift_Date
AND A.SEQ_NBR = #Sequence_Number
End-SQL
Hello,
The column A.PUNCH_DTTM is probably defined as TIMESTAMP or TIMESTAMP WITH TIME ZONE, and then, subtracting it from a DATE will produce an INTERVAL DAY TO SECOND
result, and *NOT* a number.
If so, then you can CAST it to a DATE, as follows:
A.TL_QUANTITY = ROUND( (TO_DATE($Date_Time_End,'YYYY-MM-DD HH24:MI:SS')
- CAST(A.PUNCH_DTTM AS DATE) )*24, 5 )
You will, however, lose the additional information in A.PUNCH_DTTM that is beyond
the information of a DATE column ( like fractions of a second and timezone information ) .
Hope this could help.