--select the correct data type here, DateTime vs SmallDateTime, 
-- as you might otherwise incurr another conversion function call 
-- on the source column!
DECLARE @FromDate DateTime 
DECLARE @ToAndIncludingDate DateTime

SET @FromDate = '2009-04-01'
SET @ToAndIncludingDate = '2009-04-15'

SELECT * 
FROM SomeTable
WHERE (SomeColumn >= @FromDate AND SomeColumn < DateAdd(Day, 1, @ToAndIncludingDate) )

CREATE PROC somereport
  @startdate datetime
  @enddate datetime
BEGIN
  -- Fix up the dates (remove time portion from start date, get 12AM the day after the end date)
  select @startdate = dbo.dbdate(@startdate), @enddate = dbo.dbdatenext(@enddate)

  -- Do the query...
  select *
  from mysource
  where somedate >= @startdate and somedate < @enddate -- Note I am selecting all records PRIOR to the next day
END

-- Remove the time portion from a date.
CREATE FUNCTION [dbo].[DBDate] (@DATE datetime)
RETURNS datetime
AS
BEGIN
  DECLARE @Date1 DateTime
  SET @Date1 = @Date
  SET @Date1 = DateAdd(ms, -1 * DatePart(ms, @Date1), @Date1)
  SET @Date1 = DateAdd(ss, -1 * DatePart(ss, @Date1), @Date1)
  SET @Date1 = DateAdd(mi, -1 * DatePart(mi, @Date1), @Date1)
  SET @Date1 = DateAdd(hh, -1 * DatePart(hh, @Date1), @Date1)
  RETURN @Date1
END

-- Get 12AM of the next day
CREATE FUNCTION [dbo].[DBDateNext] (@DATE datetime)
RETURNS datetime
AS
BEGIN
  RETURN DateAdd(dd, 1, dbo.DBDate(@Date))
END

• There is an index on a persisted column in the table that is defined as datediff(day,d1,d2); or


• There is an index on an indexed view column which resolves to datediff(day,d1,d2)