Search code examples
sqloracledml

Date column not being found when delete

I have this statement which does not delete any rows.

DELETE FROM mytable
WHERE
    datecolumn = sysdate + 100;

However, the below one deletes 57K rows, as I would expect. Why is this happening? Datecolumn in mytable is stored as DATE variable type.

DELETE FROM mytable
WHERE
    to_char(datecolumn, 'DD/MM/YYYY') = to_char(sysdate + 100, 'DD/MM/YYYY');
Solution

  • That's most probably because DATECOLUMN contains a "truncated" date, i.e. value that doesn't have a time component (because it is set to 00:00:00).

    Sysdate, on the other hand, returns full date + time value, so there's no match and DELETE doesn't delete anything.

    Instead of your 2nd code (which uses TO_CHAR and prevents possible index on datecolumn to be used), modify your 1st query a little bit:

    DELETE FROM mytable
WHERE datecolumn = trunc(sysdate) + 100;