I'm new here and I need your help. I have a query that returns me the error ORA30081: invalid data type for datetime / interval arithmetic.
First of all, it is important to inform you that the fields :date_fin and :data_ini are parameters with the DateTime datatype, where:
: data_ini = '02 / 01/2018 '/ * in the format' DD / MM / YYYY '* / : data_fim = '10 / 01/2018 '/ * in the format' DD / MM / YYYY '* /
Follow the code:
SELECT
CASE
WHEN:data_fim BETWEEN TRUNC( GOZOINIFER ) AND TRUNC( GOZOFINFER ) THEN:data_fim
WHEN TRUNC( GOZOFINFER ) BETWEEN:data_ini AND:data_fim THEN TRUNC( GOZOFINFER )
END - CASE
WHEN:data_ini BETWEEN TRUNC( GOZOINIFER ) AND TRUNC( GOZOFINFER ) THEN:data_ini
WHEN TRUNC( GOZOINIFER ) BETWEEN:data_ini AND:data_fim THEN TRUNC( GOZOINIFER )
END + 1
FROM
FLP_FERIAS
WHERE
FLP_FERIAS.CODINTFUNC = 217
AND STATUSFERIAS = 'N'
AND(
TRUNC( GOZOINIFER ) BETWEEN:data_ini AND:data_fim
OR TRUNC( GOZOFINFER ) BETWEEN:data_ini AND:data_fim
)