You didn't read the blogs.
The solution for time related problems is to never use between, but rather
set @StartDate =getdate()
set @Tomorrow = dateadd(day,1+datediff(day,'19000101',@StartDate),'19000101') -- Tomorrow midnight
select * from myTable where DateField>=@StartDate and DateField <@Tomorrow
Premature optimization is the root of all evil in programming. (c) by Donald Knuth
Naomi Nosonovsky, Sr. Programmer-Analyst
My blog
EDITORIAL FOR THOSE THAT READ THIS POST IN THE FUTURE:
Please pay attention to what Naomi has discussed. For general solutions I would not choose nor recommend the solution chosen by the person that posted the question. First, the date/time chosen is technically not the highest date/time possible
which therefore means that it is not "End of Day". This is splitting hairs but it frequently does matter. I understand that this might not matter for this particular problem and therefore the solution chosen might work 100% of the time for the
circumstance of this particular user.
I am also a believer in using the WHERE clause that is composed of inequalities as suggested by Naomi. Again, I would not choose nor recommend using a BETWEEN comparator for this purpose rather than inequalities for the current state of the SQL Server
Query Optimizer.
where DateField>=@StartDate and DateField <@Tomorrow
This where clause outlines a good approach for date ranges using the datetime datatype when the range is for whole days:
+ This is intended for the datetime datatype and not for the date datatype
+ Use a greater than or less than comparator for the start date/time
+ Use the start date as the lower bound
+ Use a strictly less than comparator for the end date/time
+ Use the beginning of the NEXT DATE as the upper bound
EDIT:
If you absolute MUST set a datetime datatype to the highest time possible on a particular date a good way of doing it is something like:
select
dateadd(ms, -3, dateadd(day, datediff(day, 0, getdate()), 1)
) as End_Of_Day,
dateadd(ms, -1, dateadd(day, datediff(day, 0, getdate()), 1)
) as Wrong_End_Of_Day
/* -------- Output: --------
End_Of_Day End_Of_Day
----------------------- -----------------------
2010-09-03 23:59:59.997 2010-09-04 00:00:00.000
*/
Notice that you cannot decrement "tomorrow" by a millisecond to get the highest time of a given date because SQL Server stores time in the datetime datatype accurate only to the 1/300th of a second.
