CODEWITHSUNDEEP
sql-server-2005constraints
Kenoyer130
Kenoyer130

Reputation: 7308

Sqlserver Table Time field constraint

In SqlServer 2005 I have a table with a TimeOfDay field that is a varchar(5). I want to limit the field value to valid times only (13:40,2:20). This is what I have so far

ALTER TABLE tbl ADD CONSTRAINT ck
CHECK (TimeOfDay like '[1-2][0-9]:[0-9][0-9]' )

I want the constraint to allow the first digit to be optional, but not having much luck.

Upvotes: 2

Views: 579

Answers (3)

Martin Smith
Martin Smith

Reputation: 453378

In order to disallow "times" such as 29:99

ALTER TABLE tbl ADD CONSTRAINT ck
CHECK (
TimeOfDay like  '[0-9]:[0-5][0-9]' OR
TimeOfDay like '1[0-9]:[0-5][0-9]' OR
TimeOfDay like '2[0-3]:[0-5][0-9]'
)

Have you considered storing as datetime with the date part set to 1 Jan 1900?

Upvotes: 1

JeffO
JeffO

Reputation: 8043

ALTER TABLE tbl ADD CONSTRAINT ck 
CHECK (
    TimeOfDay like '[1-2][0-9]:[0-9][0-9]' 
    OR TimeOfDay like '[0-9]:[0-9][0-9]'
)

Upvotes: 0

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171451

ALTER TABLE tbl ADD CONSTRAINT ck 
CHECK (
    (LEN(TimeOfDay) = 5 and TimeOfDay like '[1-2][0-9]:[0-9][0-9]')
        or (LEN(TimeOfDay) = 4 and TimeOfDay like '[0-9]:[0-9][0-9]')
)

Upvotes: 2

Related Questions