Nazmul

Need help.

I want to filter a DataTable by one of it's date value....and only by the month portion of that date value. For example, I want to get all the row of month June.

Any option in DataTable.Select(...) method or DataTable.DefaultView.Sort property...other than looping the table

Regards.


nzhuda@gmail.com


Re: .NET Framework Data Access and Storage Filtering Problem of DataTable

boban.s

Did you tried something like this:
yourDataTable.Select("MONTH(DateColumn) = 6");






Re: .NET Framework Data Access and Storage Filtering Problem of DataTable

Nazmul

Yes, it says month is an undefined call....




Re: .NET Framework Data Access and Storage Filtering Problem of DataTable

Greg Quinn

From what I know only the most basic comparison functions are included in the DataTable.Select method.

I'd suggest doing your filtering on the database side in your SQL. Or create a seperate column for your month values and then filter on that.





Re: .NET Framework Data Access and Storage Filtering Problem of DataTable

BabyFace Jay

Hi, because you mentioned only a DataTable, I assume you are not binding it to a BindingSource If you did bind to a bindingSource, you could do something like:

Code Snippet
this.bindingSource.Filter = "'6/1/2007 12:00:00 AM' <= Date AND Date <= '6/30/2007 11:59:59 PM'";

You would have gotten those dates in June.

If you do not wish to use BindingSource, then you could use the DataTable.Select(...) with the above filtering options.

Hope it helps





Re: .NET Framework Data Access and Storage Filtering Problem of DataTable

Nazmul

Actualy the project in such a stage that, it will be difficult to add new colum.

I want June Data...for every year...not a particular year.




Re: .NET Framework Data Access and Storage Filtering Problem of DataTable

Bruno Guardia (MSFT)

Why is it difficult to add a new column

You don't need to add the column to the table in the database, only to the query. I suppose you are creating your DataTable by a query like:

select * from DataTable

If you change your query to:

select *, month(DateColumn) as MonthOfTheYear from DataTable

Your DataTable in memory will have the MonthOfTheYear property, and you could do

DataTable.Select("MonthOfTheYear=6")

- Bruno





Re: .NET Framework Data Access and Storage Filtering Problem of DataTable

BabyFace Jay

Hi, you do not need to add a new column because you definitely already have one column for Date right

Since you mentioned every year and not a particular year, I assume that you knew the start and end year If yes, you could use my method in previous post to filter but you will need to construct by using AND and OR such as

Code Snippet

this.bindingSource.Filter = "('6/1/2007 12:00:00 AM' <= Date AND Date <= '6/30/2007 11:59:59 PM')"

+ " OR ('6/1/2008 12:00:00 AM' <= Date AND Date <= '6/30/2008 11:59:59 PM') ";

+ " OR ('6/1/2009 12:00:00 AM' <= Date AND Date <= '6/30/2009 11:59:59 PM') ";

Although I show hardcoded years, you could easily use a StringBuilder and string.format() to build the filter string.





Re: .NET Framework Data Access and Storage Filtering Problem of DataTable

Nazmul

Thanks for the suggestion. Though I have some comments/points:

@Bruno
True, it is not a difficult job to add extra colum in table or in SQL....

But the design of the project in such a way that, there is no option to add extra field in query or table directly that does not exist in table schema. Well, I dont want to argue on design as this design required this feature. Lots of issues are there.

If built in select/filter function does not have some query option, then I may have to write my on filterering funtion.


@Babyface
Your query may work if I knew the end date. Problem is I don't now that. it may (must) have future date, as the system is on production planning...

Also, if the data is too high (example: 100000 row) and in worst case...if there are 50~100 different year, then I am afraid of the size of query string and the the time to build the query string...and the resulting performance. May be loop through and deleting unmatched data will perform better.

Actually I am looking for any built in silution for that. I dont know whether 'format' can apply in select method. Do you guys have some idea on that




Re: .NET Framework Data Access and Storage Filtering Problem of DataTable

boban.s

There is no simple way of achieve the required filter of records. By the way, Select Method internaly loops all records and apply expression to every record and if pass adds to DataRow array as a result. You can loop all records and check every row's DateColumn and if date's month is 6 then add to DataRow array as a result. Here is some code:

DataSet1 ds = new DataSet1();

DataSet1.DataTable1Row row = ds.DataTable1.NewDataTable1Row();

row.FirstName = "Boban";

row.LastName = "Stojanovski";

row.Address = "ul:500 br:98a";

row.Date = DateTime.Today;

ds.DataTable1.Rows.Add(row);

row = ds.DataTable1.NewDataTable1Row();

row.FirstName = "Petre";

row.LastName = "Petrevski";

row.Address = "ul:500 br:98a";

row.Date = DateTime.Today.AddMonths(-3);

ds.DataTable1.Rows.Add(row);

List<DataRow> rows = new List<DataRow>();

foreach (DataSet1.DataTable1Row tableRow in ds.DataTable1.Rows)

{

if (tableRow.Date.Month == 6)

rows.Add(tableRow);

}

//here rows will contain one row which is with month = 6


Where DataSet1 is typed dataset which have one table named DataTable1. Table contains four columns where Date is of DateTime type.






Re: .NET Framework Data Access and Storage Filtering Problem of DataTable

Nazmul

@boban

Thanks for your answer with a sample. Actualy I was also thinking in same way, if I could not find any shortcut solution in DataTable.Select() method.

Seems no other way.

Regards