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?

Solution

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

Comments

[ericosg]

personally i still use the date type and reset the time to 0.

[Hieu Nguyen Trung]

@ericosg how can you reset the time to 0?

[ericosg]

i think Rob van Wijk answered it quite well

[cartbeforehorse]

I think this problem needs to be elaborated upon. It becomes a real problem not to have a JUST_DATE datatype when you want to establish a PK on the DATE column in question. If you want your table to contain only one entry per date, then you either have to ensure that the data is clean before you INSERT (i.e. You have to program it into your API), or you need to apply a constraint to your column, as described by @Rob-van-wijk in his (first) point 2.

[Hieu Nguyen Trung]

Thanks so much, the best solution for me is: update all DATE column first then add trigger before insert a row :)

[David Aldridge]

Be aware that when some versions add the check constraint into the execution plan (for example if you said "where yourdatecolumn = ...") they can calculate an incorrectly small cardinality estimate as a result. I think it was 10.1

[M-D]

Is there no "in-built" datatype in Oracle that stores only date (without time and time zone, etc) ?

[Rob van Wijk]

No, a DATE column with a check constraint is what comes closest.