Sql Query with Date range

Question

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 !!

Solution

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

Comments

[Syed Ibrahim]

Thanks a lot.. i have got it fixed :)

[Royi Namir]

@SyedIbrahim youre welcome. please also read the conversation between me and marc and mikael. there were some important points to notice about. ( and learn [also for me])