Error comparing dates ORA-00932

3

I'm having a problem comparing 2 dates in oracle. I am using the case method to compare the dates and at the end this should return to my table a number, which in the case represents an age, however, I get p error reporting:

PL / SQL: ORA-00932: Inconsistent data types: DATE expected to get NUMBER.

Below is the code snippet that gives the error.

(case when T1.ANO_MODELO = 0 then 0 else (case when ((W_REG.DAT_ENVIO_CALCULO) - T1.ANO_MODELO) < 0 then 0 else ((W_REG.DAT_ENVIO_CALCULO) - T1.ANO_MODELO) End) End)
    
asked by anonymous 21.07.2014 / 14:43

1 answer

2

All the results of your case should return the same data type. The error occurs because in a situation it returns a number and in another situation it returns a date .

Is W_REG.DAT_ENVIO_CALCULO a date ? T1.ANO_MODELO is a number ?

[EDIT]

Where you do it:

((W_REG.DAT_ENVIO_CALCULO) - T1.ANO_MODELO)

Replace with:

trunc(
(
W_REG.DAT_ENVIO_CALCULO - to_date(extract(day from W_REG.DAT_ENVIO_CALCULO) || '/' || extract(month from W_REG.DAT_ENVIO_CALCULO) || '/' || T1.ANO_MODELO, 'dd/mm/yyyy')
) / 365 )

This "formula" above will return an integer value that represents the vehicle's number of years. Take a test and see if it works out.

    
21.07.2014 / 15:28