Syed Ibrahim Syed Ibrahim - 4 months ago 19
SQL Question

Sql Query with Date range

I am trying to fetch records from the table using the below sql query.

SELECT Code,Description FROM Table
WHERE ID= 1 AND FromDate >= '2010-02-14' AND ToDate <= '2012-03-14'


Even though records exists for this date, query returns nothing.

ID HID HCode HDescription FromDate ToDate
-------------------------------------------------------------------
1 3 H8 New Year 2012-03-14 12:38:00 2012-03-14 12:38:00


Please give me a suitable solution. Thanks for your time !!

Answer

try this :

declare @dayAfter datetime     --let take 1 day after

set @dayAfter = DateAdd(day,1,'20120314')

SELECT Code,Description FROM Table
WHERE ID= 1 AND 
FromDate >= '20100214' AND 
ToDate <  DateAdd(day, DateDiff(day, 0, @dayAfter ), 0)

p.s :

DateAdd(day, DateDiff(day, 0, @dayAfter ), 0) will reset time to 00:00

so you need desired EndTime < begining of the day after