Invoke code Filter data rows by date

Help Studio
, , ,
1

Hello all,

I would like to filter a data table.
If the Due Date column is > DateTime.Today.AddMonths(3).AddMonths(1).AddDays(-DateTime.Today.Day, I want to keep the rows.

I have the following table:
Due Date Days Overdue
16.10.2021 646
08.02.2024 531
03.05.2022 447

My Invoke Code:
Dim ThreeMonths As DateTime = DateTime.Today.AddMonths(3).AddMonths(1).AddDays(-DateTime.Today.Day)
Try
Dim filteredRows = dtDateSort.AsEnumerable().Where(Function(row) DateTime.ParseExact(row(“Due Date”).ToString(), “dd.MM.yyyy”, Nothing) < ThreeMonths).CopyToDataTable()
Catch ex As Exception
Throw ex
End Try

Arguments: dtDateSort in/Out Datatable dtDateSort

The code seems to be theoretically correct, but it does not work.

Can anyone help me?

Many thanks and best regards

2

Hi @SKAYMUE

Please try this

Dim ThreeMonths As DateTime = DateTime.Today.AddMonths(3).AddMonths(1).AddDays(-DateTime.Today.Day)
Try
    Dim filteredRows = dtDateSort.AsEnumerable().Where(Function(row) DateTime.ParseExact(row("Due Date").ToString(), "dd.MM.yyyy", Nothing) > ThreeMonths).CopyToDataTable()
    dtDateSort = filteredRows ' Assign the filtered DataTable back to the original DataTable
Catch ex As Exception
    Throw ex
End Try

Regards,

3

Hi @SKAYMUE

Try this syntax below:

Dim ThreeMonths As DateTime = DateTime.Today.AddMonths(3).AddMonths(1).AddDays(-DateTime.Today.Day)
Try
    Dim filteredRows = dtDateSort.AsEnumerable().Where(Function(row) DateTime.ParseExact(row("Due Date").ToString(), "dd.MM.yyyy", Nothing) > ThreeMonths).CopyToDataTable()
    ' Use the filteredRows DataTable as needed
Catch ex As Exception
    Throw ex
End Try

Hope it helps!!

4

Hi @SKAYMUE

Try
Dim filteredRows = dtDateSort.AsEnumerable().Where(Function(row) DateTime.TryParseExact(row(“Due Date”).ToString(), “dd.MM.yyyy”, CultureInfo.InvariantCulture, DateTimeStyles.None, Nothing) < ThreeMonths).CopyToDataTable()
’ Do something with the filteredRows datatable here…
Catch ex As Exception
Throw ex
End Try

5

Many Thanks!! @lrtetala

1 Like
6

@SKAYMUE

Ideally you dont need an invoke code you can do in assign directly…anyways…its not working because you are not passing the modified datatable filteredrows out

dtDateSort = dtDateSort.AsEnumerable().Where(Function(row) DateTime.ParseExact(row("Due Date").ToString(), "dd.MM.yyyy", Nothing) < ThreeMonths).CopyToDataTable()

cheers

1 Like
7

Many Thanks!! @pravallikapaluri

8

It works for a small test table.
for the actual table:
Invoke Code:Exception has been thrown by target of invocation.

I suspect it’s related to the date in a row, but I don’t know which one and how to catch it.

9

@SKAYMUE

Try this

dtDateSort.AsEnumerable().Where(Function(row) If(DateTime.TryParseExact(row("Due Date").ToString(),"dd.MM.yyyy",Nothing,Nothing,Nothing),DateTime.ParseExact(row("Due Date").ToString(), "dd.MM.yyyy", Nothing) < ThreeMonths,False)).CopyToDataTable()

Also to check exception details you can open locals panel and check the exception details from there

cheers

10

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