ORA-01861 error when comparing dates sysdate-CodePudding

SELECT
    DISTINCT rc.ID AS ID_ROBOT_CARGA,
    ral.CIF AS CIF,
    rc.ID_CONTRATO AS REFERENCIA,
    CASE
        WHEN rc.RESPUESTA_INTERCHANGE = 1 THEN 'SI'
        WHEN rc.RESPUESTA_INTERCHANGE = 0 THEN 'NO'
        ELSE NULL
    END AS POPUP_INTERCHANGE
FROM
    ROBOT_CARGA rc
FULL JOIN ROBOT_ALTA_LINEA ral ON
    rc.ID = ral.ID_ROBOT_CARGA
WHERE
    rc.RESPUESTA_INTERCHANGE = 1
    AND rc.FECHA_RESPUESTA_INTERCHANGE > TO_CHAR(TRUNC(SYSDATE, 'day'), 'yyyy-mm-dd hh24:mi:ss'); --FAILING HERE

So what i want it's taking the day of today in a WHERE condition but an error ORA-01861 it's been throwing i guess it's because of SYSDATE..

CodePudding user response：

What is FECHA_RESPUESTA_INTERCHANGE column's datatype? Should be DATE.

If so, then don't compare it to a string. SYSDATE is a function that returns DATE, so that would be just

and rc.FECHA_RESPUESTA_INTERCHANGE > trunc(sysdate)

Example which shows that it works:

Setting date format (you don't have to do that; it is irrelevant):

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

Sample table:

SQL> create table robot_carga as
  2  select sysdate - 2 fecha_respuesta_interchange from dual;

Table created.

Query which comapres fecha_respuesta_interchange to trunc(sysdate):

SQL> select *
  2  from robot_carga
  3  where fecha_respuesta_interchange < trunc(sysdate);

FECHA_RESPUESTA_INT
-------------------
08.01.2022 14:35:46

SQL>

See? No error.