date ranges and half-open intervals

Half-open intervals:
http://hotmath.com/hotmath_help/topics/interval-notation.html
http://mathworld.wolfram.com/Half-ClosedInterval.html

Suggested way to store date ranges with TIMESTAMP fields:
http://stackoverflow.com/questions/156032/how-do-you-store-date-ranges-which-are-actually-timestamps

Above discussion particularly relevant to SQL Server 2005 which does not have native support for DATE, only TIMESTAMP (which they call DATETIME for historical reasons).

We need to record start and end dates of services.  Services dates are assumed inclusive, in other words the service will be available for the entire first day and last day as well as the days in between.  Our problem is SQL Server 2005 forces us to use a DATETIME (or SMALLDATETIME) type, so we have to specify a time component as well as a date.  For start date the start time is easy: 00:00:00, for end date the end time is more tricky.  We bill to the second so we could say 23:59:59 (this would be considered a closed interval) or we could say 23:59:59.999 (ie approaching the boundary) or even 00:00:00 the next day.  There are advantages and disadvantages to all options but in the end we went for the third option in the database, but display inclusive end date to the users as it's more intuitive (ie we followed deepc's advice).

Joe Celko's book Thinking in Sets, he seems to favour the second approach.  This thread mentions the different approaches (Kornelis seems to favour the third approach):
http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-programming/66078/Date-Range

If you get to use SQL Server 2008 - lucky you - make use of the new data types:
http://www.simple-talk.com/sql/t-sql-programming/time-gentlemen-please!-the-sql-server-temporal-datatypes/