ADD time 23:59:59.999 to end date for between

One option that avoids needing to add EndDate + 23:59:59.999 is to not use the between comparison and instead use column_name >= @StartDate and column_name < @EndDate +1

Please note the accuracy and rounding of the DATETIME type in SQL Server 2005:

datetime values are rounded to increments of .000, .003, or .007 seconds

SQL Server 2008 introduced the DATETIME2 type which has an accuracy of 100 nanoseconds. So in SQL Server 2008 you could do:

DECLARE @d DATETIME = '2011-10-07 00:00:00.000'
SELECT DATEADD(MS, -1, DATEADD(D, 1, CONVERT(DATETIME2, @d)))

Alternatively you may want to avoid the BETWEEN operator in this case:

@StartDate <= Column_Name AND Column_Name < DATEADD(D, 1, @EndDate)

Since the advent of datetime2 datatype, I have been struggling with this problem. To calculate the end of day as a datetime2 datatype I add the number of seconds in a day to the =date= then subtract 100 nanoseconds. Voila:

declare @bod datetime2

declare @eod datetime2 

set @bod = cast (GETDATE() as DATE) 

set @eod = DATEADD(ns, -100, DATEADD(s, 86400, @bod))

print @bod

print @eod

-- answer:

2013-12-01 00:00:00.0000000

2013-12-01 23:59:59.9999999

Now I'm off to datetimeoffset data type.