Query to return data from SQL Server table based on date criteria

I have the following piece of code in C# that I use to select some rows from a SQL Server table based on a date criteria.

DateTime From, DateTime To
SqlParameter[] oParam = new SqlParameter[3];
oParam[0] = new SqlParameter("@From", From.Date);
oParam[1] = new SqlParameter("@To", To.Date);
DataTable dt = clsDatabaseHistory.ExecuteReader("SELECT * FROM tblHistory WHERE Date_Requested BETWEEN @From and @To", oParam);

If for example From=18/08/2011 and To=18/08/2011 and there is data in the table tblHistory that has the Date_Requested value as 18/08/2011 the query does not return it.

But if I change the value of To from 18/08/2011 to To=19/08/2011 the query returns me all the values from the table that have a Date_Requested value of 18/08/2011, but none from the 19/08/2011.

How can something like that be possible and what query should I use to return rows where the dat开发者_开发百科e field is between date1 and date2.

Something like :

select * rows where datevalue >= date1 and datevalue <= date2

Thank you.

Change your query to use >= and <

select * rows where datevalue >= date1 and datevalue < date2 + 1

I bet your Date_Requested in your table also has some time associated with it - so it probably really is 18/08/2011 14:37 or something like that.

The BETWEEN clause will be selecting anything between 18/08/2011 00:00:00 and 18/08/2011 00:00:00 - basically nothing.

What you need to take into account when working with DATETIME is the fact there's always also TIME involved!

If you want everything for today, you need to use:

BETWEEN `18/08/2011 00:00:00` AND `18/08/2011 23:59:59` 

With those two values, you should get all rows with a Date_Requested of today.

OR: in SQL Server 2008 and newer, you could also use the DATE column type which stores date only - no time portion involved. In that case, you should be fine with your query values.

From.Date and To.Date gets the date portion on c# side ignoring the time portion; you need to do something similar on the database side.

Try

"SELECT * FROM tblHistory WHERE cast(Date_Requested as DATE) BETWEEN @From and @To"

to remove the time portion.

EDIT: as explained in this answer, you could change @To param value to

      oParam[1] = new SqlParameter("@To", To.Date.AddDays(1));

You need to account for time (not just date). One way I handle that is like this:

SELECT
...
WHERE CONVERT(varchar(8), date_begin, 112) <= convert(varchar(8), @to, 112)

This converts dates to YYYYMMDD format (with no time), which is very easy to use in <, >, = comparrisons.

Ok, thanks to you all, i've done the following thing

oParam[2] = new SqlParameter("@To", To.Date.AddDays(1));

and used the following select

SELECT * from MyTable WHERE CONVERT(varchar(8), Date_Requested, 112) >= CONVERT(varchar(8), @From, 112) and CONVERT(varchar(8), Date_Requested, 112) < CONVERT(varchar(8), @To, 112)

Datetime variables must be converted to be compared.

Thanks all!