Problem in Retrieving records by giving from and to date

Question

Hi all,

I have a Problem in Retrieving records by giving from and to date. I want to fetch record for date Feb 08 2010, and I should use To and From date fields. I tried:

select * from dbo.BuzzMaster
where date >=  '2/7/2010'
  and date <=  '2/8/2010'

Output:

Not able to retrieve records for date '2/8/2010' 

I get the record for date '2/8/2010' only if I give:

select * from dbo.BuzzMaster
where date >=  '2/7/2010'
  and date <=  '2/9/2010'

(or)

select * from dbo.BuzzMaster
where date >=  '2/8/2010'
  and date <=  '2/9/2010'

So how do I do that by selecting date 2/7/2010 and '2/8/2010'?

Thanks in advance.

Naveen

Answer 1

Do those dates have a time part as well? If they have, you can get the records for 2/8 like this:

WHERE Date >= '2/8/2010' AND Date < '2/9/2010'

This is because '2/8/2010 12:34' > '2/8/2010'.

(An other thing is, that I prefer writing the dates in the 'yyyy-mm-dd' format in TSQL, which is not culture sensitive.)

Answer 2

If the date contains a timepart then you can get the required result by issuing the following TSQL

select * from dbo.BuzzMaster 
where DATEADD(dd, 0, DATEDIFF(dd, 0, date))>=  '2/7/2010' 
  and DATEADD(dd, 0, DATEDIFF(dd, 0, date))<=  '2/8/2010' 

This will extract the date part from your date.

Answer 3

use from this query:

select * from dbo.BuzzMaster
where date between '2/7/2010' and '2/8/2010'