Sqlserver Table Time field constraint

Question

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.

Answer 1

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]')
) 

Answer 2

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]'
) 

Answer 3

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?