CODEWITHSUNDEEP
c#datetimedatatablesql-server-cerowfilter
Sam
Sam

Reputation: 5536

Filtering by date with DataTable.RowFilter while ignoring time

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#

Upvotes: 4

Views: 7854

Answers (3)

Awais
Awais

Reputation: 11

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

Upvotes: 1

Digicoder
Digicoder

Reputation: 1875

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.

Upvotes: 1

CResults
CResults

Reputation: 5105

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'

Upvotes: 4

Related Questions