whenever you are comparing against datetime column, make sure to use correct condition... Observe the below outputs
declare @dteStart datetime, @dteEnd datetime
set @dteStart = DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
set @dteEnd = DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1)
declare @t table
(
id int identity(1,1),
dateval datetime
)
insert @t (dateval)
select GETDATE() union all
select GETDATE()-0.4 union all
select dateadd(dd,datediff(dd,0,GETDATE()),1)
--Sample Data
SELECT * FROM @t
-- using greater and less than operators rather BETWEEN
SELECT *
FROM @t
WHERE dateval between @dteStart and @dteEnd
--OUTPUT
id dateval
1 2014-09-30 19:16:59.590
2 2014-09-30 09:40:59.590
3 2014-10-01 00:00:00.000
-- using BETWEEN operator
SELECT *
FROM @t
WHERE dateval >= @dteStart and dateval < @dteEnd
OUTPUT:
id dateval
1 2014-09-30 19:16:59.590
2 2014-09-30 09:40:59.590