I am pretty sure that use of datediff won't work well with indexes, since it requires a calculation to be performed on every row that is being compared.

When I am working with dates, I always say >= the begin date, and then = @startdate and somedate < @enddate -- Note I am selecting all records PRIOR to the next day

END

[/code]

Since the @enddate is effectively the beginning of the day AFTER the date required, saying < will then get all records up to and including the end of the day requested...

Here are the date functions I use. Unfortunately I cannot remember if I wrote them myself or found them somewhere... My dbdate function used to use the convert function to get a string representation and then truncate and convert the result back into a date, but I ran into trouble with different regional settings (where dates and times are expressed differently). The method below is a bit slower and but seems to work for any regional setting.

-- 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