When searching between datetimes in SQL(or SQL in a Stored Proc) we can't just do:
select * from <table>
where CreateTime >= @DateFrom and CreateTime <= @DateTo
If your search is date inclusive and you're not concerned with time, which most users aren't, then you need to add a little extra code to get the right results. Basically, you need to add one day to @DateTo and trunc. Here's a Sql Server 2005 function that will help:
CREATE FUNCTION [dbo].[trunc]
(
@dt datetime
)
RETURNS datetime
AS
BEGIN
return dateadd(day, datediff(day, 0, @dt), 0);
END
Now your SQL should be:
select * from <table>
where CreateTime >= dbo.trunc(@DateFrom)
and CreateTime < dbo.trunc(@DateTo + 1)
If you want more info about this then go to:
Accurate DATETIME Range Searching in SQL Server[
^]
