Home » SQL & PL/SQL » SQL & PL/SQL » DATE datatype should accept only date.
DATE datatype should accept only date. [message #230158] Wed, 11 April 2007 01:51 Go to next message
Messages: 88
Registered: April 2007
Location: CHENNAI
I am using oracle 10g ,my problem is DATE datetype should accept only date.It should not accept date with time from the user.
I am using nearly 200 tables,in that nearly 450 columns having DATE datatype.
For all 450 columns using trunc(xdate) or to_date(xdate,'dd-mon-yyyy') or to_char(xdate,'dd-mon-yyyy') is very difculty.
Is there any way to accept only date( by mistake if a user enters date with timestamp it should not accept) in the create table statement itself?

Re: DATE datatype should accept only date. [message #230162 is a reply to message #230158] Wed, 11 April 2007 02:06 Go to previous message
Michel Cadot
Messages: 63920
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Add a check constraint:
SQL> create table t (mycol date check (mycol=trunc(mycol)));

Table created.

SQL> insert into t values (sysdate);
insert into t values (sysdate)
ERROR at line 1:
ORA-02290: check constraint (MICHEL.SYS_C008513) violated

SQL> insert into t values (to_date('01/01/2007','DD/MM/YYYY'));

1 row created.

Previous Topic: How to optimize this SQL statement
Next Topic: Need help in writing pl/sql query
Goto Forum:

Current Time: Tue Oct 25 04:18:50 CDT 2016

Total time taken to generate the page: 0.12811 seconds