I am creating a table for an application that handles scheduling and deals with recurring events. I need to indicate which days and times an event may recur on (eg. every Monday and Wednesday at 1pm). Is there a datatype that can handle only days of the week without inputting specific dates, or will I need to create another table containing days of the week and reference these with a FK? This is obviously undesirable because it will make handling events that start before midnight and end after midnight more complex.
+2
A:
Use a smallint for the Day of Week.
If you are using SQL Server 2008, there is a new time datatype for the time of day column, otherwise you will still need to use a datetime datatype.
Mitch Wheat
2010-02-27 01:44:24
One thing I'd add is to create a `CHECK` constraint on the column requiring the value to be between 1 and 7 (or 0 and 6, depending on the implementation). Also... why smallint and not tinyint?
Aaronaught
2010-02-27 02:26:26
@Aaronaught: good point.
Mitch Wheat
2010-02-27 02:35:44