T-SQL has a handy BETWEEN statement that makes it easy to apply date range criteria. The basic usage is
WHERE dtSomeDate BETWEEN '1/1/2007' and '1/31/2007'
...which can replace the cumbersome
WHERE dtSomeDate >= '1/1/2007' AND dtSomeDate <= '1/31/2007'
either expression would (in theory) retrieve any date during January 2007. But SQL Server dates can contain a time component - so if dtSomeDate was actually '1/31/2007 08:00:00' meaning 8:00 AM on the 31st, that date actually would fall outside the desired range.
I picked up a tip from SqlServerCentral that removes the time - you could write
and I even re-posted that tip at my web site, but every time I need to use it, I have to go look it up. So I wrote the following function that is easy to remember & can be invoked whenever comparing dates:
CREATE FUNCTION dbo.fnNoTime
DECLARE @date smalldatetime
SET @date = DATEADD(d,DATEDIFF(d,0,@DateTime),0)
...returning to my original example, you would use the function as follows:
WHERE dbo.fnNoTime(dtSomeDate) BETWEEN '1/1/2007' and '1/31/2007'