Register Login

ORA-00932: Inconsistent Datatypes: Expected Number Got Interval Day to Second Error While Updating a SQL

Updated May 19, 2018


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 


Comments

  • 24 Apr 2014 11:11 am Chandan Singh Parihar Best Answer


    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. 


×