5.9 Querying Periods5.9.1 ProblemYou 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 SolutionUse 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 DiscussionThe 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 . |