Using BETWEEN with dates

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
DATEADD(d,DATEDIFF(d,0,dtSomeDate),0)
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
(@DateTime datetime)
RETURNS datetime
AS
BEGIN
DECLARE @date smalldatetime
SET @date = DATEADD(d,DATEDIFF(d,0,@DateTime),0)
RETURN @date
END

...returning to my original example, you would use the function as follows:

WHERE dbo.fnNoTime(dtSomeDate) BETWEEN '1/1/2007' and '1/31/2007'