Filtering by date with DataTable.RowFilter while ignoring time

Question

How do I filter for records on a DataTable which are on a certain date?

I've tried plain [datecol] = #11 March 2010# and CONVERT([datecol],'System.DateTime') = #11 March 2010#. With no luck.

MSDN: RowFilter Expression Syntax

Solution

[datecol] >= #11 March 2010 00:00# AND [datecol] <= #11 March 2010 23:59:59#

Answer 1

select 
   ...
   ...
   ...

where [datecol] between '11 March 2010 00:00:00' and '11 March 2010 23:59:59'

Sorry, wrong head on. Just been writing a LOT of SQL!!

Try this

[datecol] >= #03/11/2010 00:00:00# AND [datecol] <= #03/11/2010 23:59:59# 

or

 [datecol] >= '03/11/2010 00:00:00' AND [datecol] <= '03/11/2010 23:59:59' 

Answer 2

select * from [X] WHERE DATEDIFF(dd,[datecol],'3/11/2010') = 0

There is a lot of power with the datediff function,

DATEADD(DAY,DATEDIFF(DAY,0,[datecol]),0) is another way you can strip the time portion from the column if you need that data for other processing. We use this if we're wanting to group items that occur during the day, or some other uses if we need to group items that occur during hours or after hours.

Answer 3

Hi,

You can use following LINQ code to filter DataTable row-set according to date:

var resultSet = from tbl in dt.AsEnumerable()
                where tbl.Field<DateTime>("ColName").ToString("dd/MMM/yyyy") == "07/Aug/2010"
                select tbl;

Further, to get the insight of basic LINQ queries on DataTables see the post Filtering DataTable using LINQ

best Rgds