Date type without time in Oracle

oracle datetime date oracle10g
Hieu Nguyen Trung picture Hieu Nguyen Trung · May 3, 2012 · Viewed 39.3k times · Source

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

Rob van Wijk picture Rob van Wijk · May 3, 2012

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

Related questions

Is java.sql.Timestamp timezone specific?

I have to store UTC dateTime in DB. I have converted the dateTime given in specific timezone to UTC. for that I followed the below code. My input dateTime is "20121225 10:00:00 Z" timezone is "Asia/Calcutta" My Server/DB(oracle) is …

Read More
How to convert date to datetime in Oracle?

i have a date in oracle with this format DD-MM-YYY and i want to convert it to datetime with this other format DD-MM-YYY HH24:MI how can i proceed? I've tried this but nothing is working : to_date(the_date,…

Read More
Oracle: Avoiding NULL value in to_date

I have a functional select statement that has a where clause, in the where clause there is a statement like so... to_date(camp.start_date, 'MM/DD/YYYY') >= to_date(:from_date, 'YYYY-MM-DD HH24:MI:SS') However, if …

Read More