Home » SQL & PL/SQL » Client Tools » Oracle datatype to store ONLY DATE without time (Oracle 9i)
Oracle datatype to store ONLY DATE without time [message #388962] Thu, 26 February 2009 15:54 Go to next message
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 #388971 is a reply to message #388962] Thu, 26 February 2009 17:04 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
TRUNC(SYSDATE)
Re: Oracle datatype to store ONLY DATE without time [message #388978 is a reply to message #388971] Thu, 26 February 2009 19:27 Go to previous messageGo to next message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
TRUNC(SYSDATE)
is that a data type?
Re: Oracle datatype to store ONLY DATE without time [message #388979 is a reply to message #388962] Thu, 26 February 2009 19:32 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Are you incapable or unwilling to Read The Fine Manual?
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions201.htm#sthref2425

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Re: Oracle datatype to store ONLY DATE without time [message #389035 is a reply to message #388962] Fri, 27 February 2009 01:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Oracle has no internal date-only datatype.
Re: Oracle datatype to store ONLY DATE without time [message #389171 is a reply to message #388962] Fri, 27 February 2009 08:36 Go to previous message
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.

Previous Topic: Way to identify DB connection in SQL developer script?
Next Topic: "Not spooling currently" Error message
Goto Forum:
  


Current Time: Sun Dec 04 14:48:27 CST 2016

Total time taken to generate the page: 0.09999 seconds