Delete rows by date in ANSI SQL

Question

I want to remove rows from last day, I have a date column, how can I do this in ansi?

delete from mytable where mydate < current_date;

This query deletes both yesterday's and today's records, I want to keep today's records ('today' is from 12 am onwards)

Answer 1

Your statement is valid ANSI SQL and will work on any DBMS that complies with the ANSI SQL specification with regards to DATE handling.

With Oracle the situation is different: a DATE column/value always contains a time as well. So current_date (or sysdate which is the same for the sake of this discussion) will not return 2014-09-17 but e.g. 2014-09-17 16:54:12.

Now if you have a row in your table that contains 2014-09-17 08:54:12 the condition mydate < current_date will be true because 08:54:12 is smaller than 16:54:12 and thus the row will be deleted.

You need to rewrite your statement to:

delete from mytable 
where trunc(mydate) < trunc(current_date);

trunc() set the time part of a DATE to 00:00:00 and thus the comparison behaves as if there was no time part involves (because it's the same for both comparison values).

If you really, really need to write this condition in ANSI SQL and taking Oracle's non-standard DATE handling into account you need to do something like this:

select * 
from mytable
  where (extract(year from mydate) < extract(year from current_date))
     or (extract(year from mydate) = extract(year from current_date) and extract(month from mydate) < extract(month from current_date))
     or (extract(year from mydate) = extract(year from current_date) and extract(month from mydate) = extract(month from current_date) and extract(day from mydate) < extract(day from current_date));

The extract() function as shown is ANSI SQL.