Filter dates less than a particular date

[jamnanin (Naresh Jamnani)]

How do i filter dates older than 2017-09-07?

[aman_saini]

Hi. Use DT.Select method to build a query that would return the filtered Data.
Have a look at the following.

Assign .Select method

Hello @poogy112, You can use dataTable.Select(“[Column-0] = ‘LISTED’”) ,This is an example FilterTable.xaml (8.6 KB) Take a loot at this: Regards, Susana

[jamnanin (Naresh Jamnani)]

but what is the syntax when it comes to dates
i am unable to select dates less than the above value

[Susana (Susana González Cacheiro)]

Hello @jamnanin,

There are many samples on the forum . Hope these links could help you:

Filter Datatable contains datetime

Hi, I have an excel sheet where Robot Start Date column is in General format. Its supposed to contains values in datetime format MM/dd/yyyy HH:mm:ss I would like to filter the datatable to create a second datatable for rows which have the same date MM/dd/yyyy I’ve tried Dt.Select(“[Robot Start Date] LIKE ‘%MM/dd/yyyy%’”).copytodatatable and it says cannot perform operand LIKE on system.string and system.datetime I cannot use filter datatable activity because it is not a table in the Excel sh…

Filter data from datatable on column that has a date value

I’ve made changes to your workflow, but I’m not able to test it since you’ve not added an “Open Browser” with it. Try this updated workflow once and let me know if this solves your problem. sample_xaml.xaml (12.1 KB)

How to Filter Excel Data by Today's Date

Hello, I have not done this using the FilterOptions but you can use an Assign activity to set an array of rows or datatable that meets your criteria. Here’s an example, arrayRows = dt.AsEnumerable().Where(Function(row) cdate(row(“column”).ToString) = Now).ToArray() or dtNew = dt.AsEnumerable().Where(Function(row) cdate(row(“column”).ToString) = Now).CopyToDataTable() The difference of the two examples are that an array of rows let’s you update values and it updates to the original datatabl…

Best Regards,
Susana

[jamnanin (Naresh Jamnani)]

none of the above solutions help me for what i am looking. can you please show me a sample assign activity where you are filtering rows less than a particular date

[ClaytonM]

Hi @jamnanin
Can you share which items you are trying to filter, like are they columns in a data table or maybe just a list of strings?

Typically, you will need to convert your date to a DateTime type in order to compare with the current date or particular date of your choice.
Take this example for instance, where the dates are contained in a column of a DataTable:

Assign activity: dateTolerance <of DateTime> = Convert.ToDate("09/07/2017")

dt1.AsEnumerable.Where(Function(r) If(IsDate(r("Date").ToString.Trim), Convert.ToDate( r("Date").ToString.Trim ) < dateTolerance, False) ).ToArray

(( .ToArray can be replaced by .CopyToDataTable if you prefer to scrap the rest of the data, but I normally recommend .ToArray for processing items ))

So that would return all DataRows where r("Date") is older than the dateTolerance variable. Notice, I converted the string to a date…

There are other times where Convert.ToDate() or CDate() will not work like when the month and day is in the wrong order (ie dd/MM instead of MM/dd), which in this case you would use:
DateTime.ParseExact()

DateTime.ParseExact Method (System)

So that is how you could do this if the dates are in a Table.

If the dates are in a different source, then you can use the same type of comparison, by first converting the string to a date, then using ‘<’ or ‘<=’ next to the other datetime variable; just as long as you convert both sides to a datetime type.

I hope this is more helpful.

Regards.

[aman_saini]

make sure your Dates extracted are in the DateTime format. If say, all dates in a Column 1 is in DateTime format. I think you can achieve it with dt.Select(“DateFrom > '” + daDateFrom + “’ AND DateTo <= '” + daDateTo + “'”), use your Column name and a date which you want to filter corresponding to.

[sprath (prath)]

Can some one help me with the below scenario
I have a data in datatable which is output from screen scraping and has dates.
I am trying to filter data on some date basis using Filter data table wizard.
Please let me know what should be syntax.
Currently I am using DT.columns(2)<=convert.toDateTime(“1-Jan-2019”) but unable to filter.

[veselim (Milazim)]

Hi @ClaytonM
How can I filter on dates that are only older than 14 days from the current date with a Filter Data Table activity? So I have a variable that gives me the date 14 days ago from today.

and I want to check if the date in excel is older then this Newdate variable and if it is i want to filter only on these.

Is there a way i can do this within the Filter Data table activity?

[ClaytonM]

@veselim Hi,

did you try it? It’s not an activity I use, so I am not sure if it converts your date strings in the data table to a date type to compare with your new date.

You would also need to decide if you want to create a new data table with this filtered data set or if you want to filter your data set to an array of rows while keeping the entire data set with it. Most cases, I find needing to filter down to an array of rows, so I can check that “something” was filtered and process them, then update the data to file. But, there’s many approaches you can take.

The method I would normally use is performing the filter in an assign, which will give me back an array of rows. Then, I can check it to make sure it filtered to something. Loop through them and update just those rows. In this method, each row within the array references to the row in the initial data type, therefore the entire data set is there rather than filtering to a new data set where you lose all the unfiltered data.

Here is an example of filtering to an array of rows:

oldRequests = allRequests.AsEnumerable.Where(Function(r) If(IsDate(r("1st Email Request (MM/DD/YY)").ToString.Trim), CDate(r("1st Email Request (MM/DD/YY)").ToString.Trim) <= Today.AddDays(-1*ageInDays), False) ).ToArray

IF activity: condition: oldRequests.Count > 0
    For each row In oldRequests //TypeArgument as DataRow

Hope I didn’t confuse you too much.

Let me know if the Filter Data Table works, cause it’s not something I have tried with comparing dates.

Regards.