datetime - Date type without time in Oracle

Question

In Oracle 10g I got a problem when using DATE type in my table. I just want my DATE field store only DATE without time automatically.

There's so much solution, I know it like using TO_CHAR and TO_DATE or TRUNC but I faced that I'm using so much procedures to insert or update data and have no time to update all of them.

How could I resolve this problem?

Answer 1

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);