Not able to Filter Date Column

Help
, ,
1

Hi All

I am attaching my exact file. Please help me to delete the records older to 2.5 months (76 days). I am not not getting correct data after applying filter using select method. It has date time and the format is Custom in the excel.

Its urgent, please. It would be great if you can provide me a working solution.

Sample.xlsx (10.7 KB)

2

Hi @kkpatel

Can you show me how you have done the filter at the moment?

3

Hi!

the trick is always in the conversion to variable type DateTime. Check out this thread or search for plenty more posts about DateTime conversion.

Cheers

4

dt_irc1.Select(“Requested >”+“'”+fromDate.ToString(“MM/dd/yyyy”)+“'”).CopyToDataTable

Also someone said : dt_irc1 = dt_irc1.AsEnumerable.Where(function(r) DateTime.ParseExact(r(“Requested”).toString,“MM/dd/yyyy hh:mm:ss tt”,nothing)>Today.AddDays(-76)).CopyToDataTable

But m not able to understand this . It errors “String is not recognized as a datetime variable.”

5

@kkpatel
for any reasons the hh:mm:ss Part leads to “String is not recognized as a datetime variable.” error
using an assign activity for a single ParseExact helped to isolate the issue

Maybe you can go for following workaround:

yourDataTableVar.AsEnumerable.Where(function ( r ) DateTime.ParseExact(r(“Requested”).toString.Substring(0,10),“MM/dd/yyyy”,CultureInfo.InvariantCulture)>Today.AddDays(-76)).CopyToDataTable

ensure that System.Globalization is imported

6

If you have DateTime type from excel to your DataTable, then it should work fine in Filter activity…