How Do I solve the problem of Oracle deleting data at a specific time？

My SQL statement is as follows：
delete from "result" where DATA_DATE < TO_DATE('2023-04-06 00:00:00', 'YYYY-MM-DD HH24-MI-SS')

the error message is：
ORA-01861: literal does not match format string
What should I do?

This is part of the information in the table：
enter image description here

If the DATA_DATE column is stored as a DATE data type:

CREATE TABLE "result" (data_date DATE);

INSERT INTO "result" (data_date)
VALUES (TO_DATE('2023-04-05 12:34:56', 'YYYY-MM-DD HH24-MI-SS'));

Then your query works:

delete from "result"
where DATA_DATE < TO_DATE('2023-04-06 00:00:00', 'YYYY-MM-DD HH24-MI-SS');

> lang-none
> 1 rows affected
>

If your DATA_DATE column is a string data type:

CREATE TABLE "result" (data_date VARCHAR2(19));

INSERT INTO "result" (data_date) VALUES ('2023-04-05 12:34:56');

Then your query:

delete from "result"
where DATA_DATE < TO_DATE('2023-04-06 00:00:00', 'YYYY-MM-DD HH24-MI-SS');

Is comparing a string to a date and Oracle will implicitly convert the string to a date so that it compares the same data types and the query is effectively:

delete from "result"
where TO_DATE(
        DATA_DATE,
        (SELECT value FROM nls_session_parameters WHERE parameter = 'NLS_DATE_FORMAT')
      )
      < TO_DATE('2023-04-06 00:00:00', 'YYYY-MM-DD HH24-MI-SS');

If the NLS_DATE_FORMAT session parameter matches:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

Then the query works.

But if the session parameter does not match:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

Then you may get the error:

> error
> ORA-01861: literal does not match format string
>

To solve the problem, either:

1. Always store dates in a DATE data type (and not as strings).

2. Do not compare different data types as Oracle will perform an implicit type conversion so that it can compare them.

   • Either compare both sides as strings:

     delete from "result"
     where DATA_DATE < '2023-04-06 00:00:00';
     

   • Or compare both sides as dates:

     delete from "result"
     where TO_DATE(data_date, 'YYYY-MM-DD HH24:MI:SS')
           < TO_DATE('2023-04-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS');
     
     
     

3. Set the NLS_DATE_FORMAT so that the implicit conversion works.

While any of the options will work; you should use #1 and fix your table so the data is stored in the correct data type. If you really can't change the table then use #2 and ensure that your queries are comparing the same data types (and, if you need to, use explicit conversions between types). Option #3 is just there for completeness and is just going to allow you to continue using bad habits of relying on implicit type conversions; don't use it.

fiddle