How to filter data table based on which date is earlier/later

Help Activities
, ,
1

Hi,
I’ve an automation where I need to filter dtTable1. That table contains a few columns including one ColumnWhatever which contains dates. The date format is MM/dd/yyyy. I have 2 variables of type DateTime with the same format - datetimeStartDate and datetimeEndDate - they have been parsed from string in an assign activity using the DateTime.ParseExact(strStartDate,“MM/dd/yyyy”,System.Globalization.CultureInfo.InvariantCulture method (and the same for the other variable). I wanted to use Filter_DataTable activity to only keep the rows that match the condition that the date from ColumnWhatever is equal or later than datetimeStartDate and also that the date from ColumnWhatever is earlier or equal to datetimeEndDate. I’ve configured the Filter_DataTable activity like so:
“ColumnWhatever” >= datetimeStartDate
“ColumnWhatever” <= datetimeEndDate
checked the keep rows option and set up the rows i want to keep in the OutputColumns panel
The output of the activity is saved to dtFiltered.
The problem I have is that dtFiltered ends up containing no rows of information, only the one with the names of columns that i have set up in the OutputColumns panel, which should not be the case, as the dates I used for testing should give me an output of 8 rows of information.
I need help understanding why this filter is not wrking and how to fix it if possible, beacuse I’ve made sure the dates are of the same format and I also know that the values from ColumnWhatever are dates, because dtTable1 that im filtering is an output of a ReadRange activity and the Excel file I’m reading has those dates displayed in a differrent format than is later saved by ReadRange ( so if I open Excel the date format is dd.MM.yyyy, but if when I checked the locals panel after ReadRange I noticed that the value from that cell is saved in dtTable1 in the format MM/dd/yyyy ). I’ve tried to change the format of datetimeStartDate and datetimeEndDate to dd.MM.yyyy , dd/MM/yyyy , MM.dd.yyyy and MM/dd/yyyy and not one has worked. Having read somewhere on the forum that date comparison is supported I am now unable to figure out what exactly needs to be done for my workflow to work, please help.

2

Hi

It may be caused by the column data is recognized string type.
Can you try the following expression?

arrDr = dtTable1.AsEnumerable.Where(Function(r) CDate(r("ColumnWhatever")) >= datetimeStartDate AndAlso CDate(r("ColumnWhatever")) <= datetimeEndDate).ToArray()

note : arrDr is array of DataRow type

image
image1107×766 40.1 KB

Regards,

1 Like
3

Unfortunately, same effect, the dtFiltered is still an empty dt with just column names after the filter.

4

HI,

If possible, can you share your specific input data? (as file ideally but it may be difficult due to user level of the forum)

Regards,

5

Actually I must apologize, your solution worked wonders ^^ It seems morning me forgot to move the assign activities i use to ParseExact String->datetime, so the datetimestartdate and datetimeenddate were null when I first tried your solution. Thank You!

1 Like
6

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