Oracle date and time datatype

Question

I tried creating a table and one of the attributes 'appdate' must have a data dtype date which includes the time as well. I've tried using the to char but then it says invalid relational operator. HOw do i do this?

SQL> create table appointment
2  (StaffID char(10), constraint appointment_staffid_fk
  3  foreign key (staffid) references staff (staffid),
  4  PatientNum varchar2(10), constraint appointment_PatientNum_fk
  5  foreign key (patientnum) references patient (patientnum),
  6  appdate date
  7  constraint appointment_appdate_ch check (to_char(appdate, "mm/dd/yyyy hh24:mi:ss"))
  8  );
constraint appointment_appdate_ch check (to_char(appdate, "mm/dd/yyyy hh24:mi:ss"))
                                                                                  *
ERROR at line 7:
ORA-00920: invalid relational operator

Answer 1

In Oracle a DATE data type always has a time component so the check constraint is unnecessary.

It is stored internally as 7- or 8- bytes:

BYTE STORES
---- --------------------------
1    TRUNC( Year / 100 ) + 100
2    MOD( Year, 100 ) + 100
3    Month
4    Day
5    Hour + 1
6    Minute + 1
7    Seconds + 1

Even if you do not set a time component, it is still stored in the database has having the time 00:00:00 (so the last 3 bytes would be each set to 1).