| Oracle datatype to store ONLY DATE without time [message #388962] |
Thu, 26 February 2009 15:54  |
Ricky_1362002
Messages: 111
Registered: February 2009
|
Senior Member |
|
|
hi all...
I was asked recently to create a table with one of the column with date datatype and yes i created it.
The developers performed some insertions with 'sysdate' into that column, and as they inserted 'sysdate', it showed date and time when queried, later they came to me and asked that they want only date to get inserted and not the time.
Is there any data type in Oracle which will store only DATE without time when we insert 'sysdate' into a column.
For now i have told them to use the trim function which will give only date, but is there any other solution?
please help...
thanks,
munna
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Oracle datatype to store ONLY DATE without time [message #389171 is a reply to message #388962] |
Fri, 27 February 2009 08:36  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Option 1) Add a trigger onto the table that fires on insert/update of each date column, and which truncates the date to remove the time component.
Option 2) Add a check constraint to each date column of the form CONSTRAINT <constraint_name> CHECK (trunc(date_col) = date_col)
This will error any time they try to insert data with a time component into the Db.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
