5.9 Querying Periods

5.9.1 Problem

You want to find all periods that include a particular date. With respect to our example, we might be interested in finding all contractors that worked on February 12, 2001. February 12, 2001 would be the date in question, and the JobStart and JobEnd dates from each schedule record would define the periods.

5.9.2 Solution

Use the BETWEEN operator to find the periods that include selected date:

SELECT 
   JobId, ContractorId, CAST(JobStart AS CHAR(12)) JobStart, 
   CAST(JobEnd AS CHAR(12)) JobEnd, JobType 
FROM ContractorsSchedules
WHERE '2001-2-12' BETWEEN JobStart AND JobEnd 

JobId      ContractorId JobStart     JobEnd       JobType 
---------- ------------ ------------ ------------ ------- 
RF10022    Bob          Feb  5 2001  Feb 15 2001  B
RF10034    Alex         Feb 11 2001  Feb 20 2001  B

The results of the query indicate that both Alex and Bob were booked on February 12, 2001.

5.9.3 Discussion

The BETWEEN operator is an inclusive operator. It is equivalent to using both the greater-than-or-equal-to (>=) and less-than-or-equal-to (<=) operators. If your problems require exclusive, or partially exclusive, results, you should use the greater-than (>) or less-than (<) operators. For example, use the following query to find all projects that started before January 12, 2001 and that ended after January 12, 2001, but which did not start or end on exactly those dates:

SELECT 
   JobId, ContractorId, CAST(JobStart AS CHAR(12)) JobStart, 
   CAST(JobEnd AS CHAR(12)) JobEnd, JobType 
FROM ContractorsSchedules
WHERE '2001-1-12' > JobStart 
  AND JobEnd > '2001-1-12'

JobId      ContractorId JobStart     JobEnd       JobType 
---------- ------------ ------------ ------------ ------- 
RF10001    Alex         Jan 11 2001  Jan 20 2001  B
RF10003    Bob          Jan  5 2001  Jan 15 2001  B

Note that we are representing date constants using strings. Using strings this way causes implicit casting (of string values to DATETIME values) in Transact-SQL. The server knows when standardized formats are in use and converts dates accordingly.