Menu
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?
347
The DATE datatype stores date and time information. Although date and time information can be represented in both character and number datatypes, the DATE datatype has special associated properties. For each DATE value, Oracle stores the following information: century, year, month, date, hour, minute, and second.
DATE Data Type For each DATE value, Oracle Database stores the following information: century, year, month, date, hour, minute, and second. You can specify a date value by: Specifying the date value as a literal.
DATETIME Values The DATETIME data type is supported by Oracle Database standard libraries and operates the same way in the OLAP DML as it does in SQL. The DATEORDER, DATEFORMAT, and MONTHNAMES options, which control the formatting of DATE values, have no effect on DATETIME values.
You can try using TRUNC() function. --- TRUNC(date_field)....in order to remove Timestamp. iif( is_date ... ( TO_CHAR(Daterec,' DD-MON-YYYY'').
The best solution would be to:
remove all times from your DATE column (update yourtable set yourdatecolumn = trunc(yourdatecolumn))
ensure that all future dates contain no time part by placing a check constraint on the column by using check (yourdatecolumn = trunc(yourdatecolumn))
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:
(Optionally) remove all times from your DATE column.
Create a before row insert or update database trigger that sets :new.yourdatecolumn := trunc(:new.yourdatecolumn);
200
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
