How to Compare Date in all Columns with DateTime Variable

Help Studio
1

Hello,

I am looping through a datatable and I have an IF condition to check if ColumnA=A. If that specific condition is met for the current row, there is another IF condition. This one is meant to check if there are any other rows in which Column=A and the date (ColumnB) is smaller than the current row’s date.

This is what I tried, but I’m getting an error:
datatable.AsEnumerable().Any(Function(x) (Datetime.ParseExact(x(“ColumnB”).ToString,“M/d/yyyy”,System.Globalization.CultureInfo.InvariantCulture) < datetime) And (x(“ColumnA”).ToString.Equals(“A”)))
in which datetime is the time variable found in the current row and M/d/yyyy is the date format found in the datatable

Thank you in advance for your help!

2

What is the error you’re receiving?

3

“String was not recognized as a valid DateTime.”

4

Could be that there’s a row in the datatable that isn’t convertable to datetime and that’s messing up the whole linq expression.

To debug, quickly loop through that whole column and convert to string and see if there are any rows that don’t come through in a proper date format.

5

There is a row which doesn’t follow that format. Is there a way to exclude specific rows based on index?

6

Perhaps use the Filter Data Table activity to remove that row and store it to a temporary datatable variable, and use that in the linq expression

7

Is there a not a way to exclude it on the IF activity? The filter activity would complicate my workflow.

8

Sure, you can add a where statement to select only rows that contain a “/” indicating it’s a date (assuming that the one problematic row does not have a slash)

datatable.AsEnumerable().where(function(x) x("ColumnB").tostring.contains("/")).Any(Function(x) (Datetime.ParseExact(x(“ColumnB”).ToString,“M/d/yyyy”,System.Globalization.CultureInfo.InvariantCulture) < datetime) And (x(“ColumnA”).ToString.Equals(“A”)))
1 Like
9

@sidb please let me know if the solution worked for you

10

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