Date type without time in Oracle

The best solution would be to:

  1. remove all times from your DATE column (update yourtable set yourdatecolumn = trunc(yourdatecolumn))

  2. ensure that all future dates contain no time part by placing a check constraint on the column by using check (yourdatecolumn = trunc(yourdatecolumn))

  3. adjust all your INSERT and UPDATE statements or -if you're lucky- adjust your API, to only insert TRUNCed dates.

The easiest solution would be to:

  1. (Optionally) remove all times from your DATE column.

  2. Create a before row insert or update database trigger that sets :new.yourdatecolumn := trunc(:new.yourdatecolumn);