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

Answer 1

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

Answer 2

Try with following query,it will definately solve your problem....

SELECT Code,Description FROM Table
   WHERE ID= 1 AND
         CONVERT(VARCHAR(10), FromDate, 101)>= CONVERT(VARCHAR(10),CAST('2010-02-14' AS DATETIME),101) AND 
         CONVERT(VARCHAR(10), ToDate, 101)<= CONVERT(VARCHAR(10),CAST('2012-03-14' AS DATETIME),101) ;

Answer 3

If you select data between FromDate and ToDate then remove ID=1. Аs in this case is selected record with ID = 1 and FromDate and ToDate checked this entry.

Answer 4

You are selecting data from ragne

FromDate >= '2010-02-14 00:00:0000' AND ToDate <= '2012-03-14 00:00:0000'

You should do like this:

FromDate >= '2010-02-14' AND ToDate < '2012-03-15'

Answer 5

SELECT *
 FROM  table
 WHERE year_date BETWEEN FromDate AND ToDate