CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Syed Ibrahim
Syed Ibrahim

Reputation: 25

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

Upvotes: 2

Views: 11966

Answers (5)

Greg
Greg

Reputation: 2609

SELECT *
 FROM  table
 WHERE year_date BETWEEN FromDate AND ToDate

Upvotes: 0

Royi Namir
Royi Namir

Reputation: 148514

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

Upvotes: 2

Jānis
Jānis

Reputation: 2266

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'

Upvotes: 0

user1082916
user1082916

Reputation:

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) ;

Upvotes: 1

BelKoD
BelKoD

Reputation: 331

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.

Upvotes: 0

Related Questions