Using DataTable extension method DataTable.AsEnumerable() to filter DataTable

I am trying to filter a data table derived from an excel sheet (itemDataTable) and filter its records that meet the below logic into another dataTable (outDataTable)

Logic: where Column(“DTC ID Status”)= “T” and Column(“As of Date”) < Convert.ToInt32(now.ToString(“yyyyyMMdd”)

I have an assign activity with the follwing value: itemDataTable.AsEnumerable().Where(function(r) r(“DTC ID Status”).ToString = “T” and Convert.ToInt32((“As of Date”).ToString) < Convert.ToInt32(now.ToString(“yyyyyMMdd”)) ).CopyToDataTable()

But I am reciving the following error: Assign: Input string was not in a correct format.

I’d like to avoid using a for each as the function is significantly faster.

Much appreciated,
Chance

The error is precisely correct.

You are converting a string to int and using your to compare date time.

Right query should be something like this.

itemDataTable.AsEnumerable().Where(function(r) r(“DTC ID Status”).ToString = “T” and Convert.ToDateTime((“As of Date”).ToString) < Convert.ToDateRime(now.ToString(“yyyyyMMdd”)) ).CopyToDataTable()

Also, it depends on the “As of Date” → it should be a valid string that can be converted to datetime

you can covert a string to datetime using parse. Below is a sample converting a string of MM/dd/yyyy to date time

Datetime.ParseExact(“As of Date” ,“MM/dd/yyyy”,System.Globalization.CultureInfo.InvariantCulture)

Thanks for the speedy response!
To clarify the ‘As of Data’ is coming in this format (yyyyMMdd) as a double.

I tried your code and got this error: Assign: The string was not recognized as a valid DateTime. There is an unknown word starting at index 0.

Datetime.ParseExact(“As of Date” ,“yyyyMMdd”,System.Globalization.CultureInfo.InvariantCulture)

That is just because the “As of Date” was not converted correctly to datetime, we need to parse it.

itemDataTable.AsEnumerable().Where(function(r) r(“DTC ID Status”).ToString = “T” and Datetime.ParseExact(“As of Date” ,“yyyyMMdd”,System.Globalization.CultureInfo.InvariantCulture) < Convert.ToDateTime(now.(“yyyyyMMdd”)) ).CopyToDataTable()

Try this, not around my system to test this but this should work

That worked! Just one slight modification on the Datetime>ParseExact → DateTime.ParseExact(r(“As of Date”).ToString,“yyyyMMdd”,System.Globalization.CultureInfo.InvariantCulture)

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.