Day or Night CHECK in SQL Server-CodePudding

I have a Time_Interval column that should only have the values "day" or "night". It will be "day" if it is from 8AM to 8PM and "night" if it is from 8PM to 8AM. use CHECK for value entry.

Maybe I need use a trigger that checks the time in the getdate?

I have this:

CREATE TABLE Calls (
  Time_Interval VARCHAR(10) CHECK (
     Time_Interval='day' 
     OR Time_Interval='night'
   )
)

CodePudding user response：

Maybe like this

CREATE TABLE Calls 
(
 Time_Interval VARCHAR(10) CHECK (case when cast(getdate() as time) BETWEEN '8:00:01' AND '20:00:00' then 'day' else 'night' end =Time_Interval)
)

CodePudding user response：

I think you can do this with a computed column. Assuming that you're storing some time-ish† value in your table, you can derive day/night like so:

CREATE TABLE dbo.Calls (
    ts time(0) not null,
    Time_Interval as 
        case when datepart(hour, ts) between 8 and 19 
        then 'day'
        else 'night'
    end
);

When I insert test data into the table, it appears correct to me.

insert into dbo.Calls (ts) values
    ('08:00'),
    ('07:59'),
    ('19:59'),
    ('20:00');

† any data type with a time component should work.