Filter dates less than a particular date

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

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

1 Like

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

Hello @jamnanin,

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

Best Regards,
Susana

2 Likes

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

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()

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.

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.

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.

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.

image

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.
image

image

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

@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.