Posted by Plamen Ratchev on 05/14/07 13:40

If you just need to select a range of dates regardless of any gaps or
intervals in the range (that is holidays, non-working days, other special
events, etc.), then you can simply use the comparison operators >, <, =, <=,
>=, or BETWEEN, for example:

WHERE DATE > '20070508'

WHERE DATE >= '20070509'

WHERE DATE > '20070508' AND DATE <='20070511'

WHERE DATE BETWEEN '20070509' AND '20070511'

Note that BETWEEN is inclusive of the start and end expressions.

Also, you can use IN to select a few particular dates:

WHERE DATE IN ('20070509', '20070512', '20070515')

If you DATE column contains values that have time different than midnight,
then you have to be careful about using the correct start/end date to
guarantee correct results.

As stated by Ed, using a calendar table is a great method to handle date
ranges, especially when you have exceptions (gaps) in the range. See one
example of creating and using a calendar table here:
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

HTH,

Plamen Ratchev
http://www.SQLStudio.com

• Next in forum: UNION SELECT from Multiple Subselects
• Prev in forum: Re: How Import XML file in a table with trigger.
• Thread view: Re: Run same query for each day across a date range?

[Reply to this message]