samo samo - 1 year ago 88
SQL Question

How to delete rows by date when time is unknown

I'm trying to delete rows for a given date from my table. Currently, it only works when I format it as follows:

delete from table_name
where date_entered = to_date('20-SEP-15 11:10:35', 'dd-MON-yy hh:mi:ss');

If I leave out the time, it just returns "0 rows deleted". If there are a large number of entries from a given day that all occurred at different times, it isn't really feasible to enter the exact time for each one in order to delete them all, especially with time not being automatically visible.

Answer Source

Use Oracle's trunc() function - it removes the time from a datetime value.

delete from table_name where trunc(date_entered) = to_date('20-SEP-15', 'dd-MON-yy');
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download