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

Your question is unclear. Could you please edit it to include some valid dates and some invalid dates, and explain why the invalid ones are invalid? — Luke Woodward, Apr 10 '17 at 20:24
once you declare your column a 'DATE' the database ensures it's stored that way, and as the answer below says, DATEs by default have a TIME component in Oracle - even if you don't see it when querying it, it's there. — thatjeffsmith, Apr 10 '17 at 21:39
Are you perhaps trying to check that the time has been set to something other than midnight? — Alex Poole, Apr 10 '17 at 22:04

score 1 · Answer 1 · edited May 23 '17 at 12:17

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).

Oracle date and time datatype

1 Answers1