SQL check DATE between two times for any day

date-arithmetic, oracle, plsql, sql

Solution

The `%` in your check constraint won't work here. Try this instead:

CHECK (
  EXTRACT(HOUR FROM transactionDateTime) < 22 AND
  EXTRACT(HOUR FROM transactionDateTime) >= 8)

Problem

I have a transaction table, and my problem is keeping track of the `transactionDateTime` column. Using SQL, I can't seem to get the `DATE` or `TIMESTAMP` to format so that any inserted data has to be between 8am and 10pm on any given day: ``` CREATE TABLE Transaction_T (transactionID NUMBER(8,0) , employeeID NUMBER(10,0) NOT NULL, customerID NUMBER(6,0) NOT NULL, transactionDate VARCHAR2(9) NOT NULL, transactionPayment VARCHAR2(10) check(transactionPayment IN('cash','credit','debit','ebt')), transactionType VARCHAR2(10) check(transactionType IN('void','return','sale')), transactionDateTime TIMESTAMP check(transactionDateTime < '% 22:00:00' AND transactionDateTime > '% 08:00:00') NOT NULL, CONSTRAINT TransactionPK PRIMARY KEY (transactionID), CONSTRAINT TransactionFK1 FOREIGN KEY (employeeID) REFERENCES Employee_T(employeeID), CONSTRAINT TranscationFK2 FOREIGN KEY (customerID) REFERENCES Customer_T(customerID)); ``` The table above is created, but when trying to insert a correct `Date(Time)`, I receive format errors regarding hours between 1 and 12 or not a valid month when the time is below 12. I've used Oracle documentation format and still no luck: ``` INSERT INTO transaction_T(transactionID, employeeID, customerID, transactionDate, transactionPayment, transactionType, transactionDateTime) VALUES(00000001, 0001, 000004, '04-APR-13','ebt','sale','04-APR-13 13:25:10'); ```

Original source