SQL DDL - 2 CHECK Constraints on 1 Attribute

Question

Below is DDL for the table I want to create. However, I want the attribute 'Appointment_datetime' to be a future date and during working hours (between 8:00AM and 5:00PM). I can get the future date part with -'CHECK (Appointment_datetime >= GETDATE()) But how do I get between 8AM and 5PM ontop of this constraint?

CREATE TABLE tAppointment
(
Appointment_ID       int        NOT NULL    PRIMARY KEY,
Appointment_datetime datetime   NOT NULL,   -- CHECK CONSTRAINTS NEEDED             
Appointment_week     int        NOT NULL,
Appointment_room     varchar(5) NOT NULL,   
Vet_ID               int        NOT NULL    REFERENCES tVet(Vet_ID),
Owner_ID             int        NOT NULL    REFERENCES tOwner(Owner_ID),
Pet_ID               int        NOT NULL    REFERENCES tPet(Pet_ID)
)

Answer 1

You can just add it in. Here is a method using the hour:

CHECK (Appointment_datetime >= GETDATE() AND
       DATEPART(HOUR, GETDATE()) NOT BETWEEN 8 AND 16
      ) 

Note: If you want to take weekends and holidays into account, that is more difficult and probably requires a user-defined function.