DATETIME comparison in SQL Server

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 346
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

Comments or Responses

Login to post response