Problem with Date Filtering a DataTable

Help Studio
1

Hi All, I am having an issue with a flow I am Creating

I have some data like this
File # Closing Date
111-1111-111 01/02/2022
222-2222-222 (blank)
333-3333-333 18/02/2022
444-4444-444 21/02/2022
555-5555-555 02/03/2022

This is imported into a datatable dtInput

image

With this criteria

image

I have then created a filter

image

And this is the result

image

I am not sure why the filter criteria isn’t working, could someone point me in the right direction please?

2

As per my understanding you required only date from below dt.
This will only work, if data is same format as provided.

Use code variable = yourstring.split(" "c)(1)
it will only take the date.

111-1111-111 01/02/2022
222-2222-222 (blank)
333-3333-333 18/02/2022
444-4444-444 21/02/2022
555-5555-555 02/03/2022

3

HI Yogesh - the Closing Date is in its own column in the datatable

4

Hey @ieptus !! Can you try this?

(From r in dt_test.Select()
	Where String.IsNullOrEmpty(r("Closing Date").toString) AndAlso Datetime.ParseExact(r("Closing Date").toString,“dd/MM/yyyy”,System.Globalization.CultureInfo.InvariantCulture) < DateCheck
	Select r).CopyToDatatable

Hope it help!

5

HI gabriel thanks for the reply where does this get placed?

6

Creat a new Datatable variable!

image
image1920×1021 81.1 KB

7

Does this need placing in a for each loop?

8

Nope! Just an assign activity!

9

sorry, was being a bit slow. dtResult is the filtered datatable

10

Yes! dt_result is the filtered Datatable

11

I get an Option strict on disallows implicit conversions from string to date error

12

Can you send a sample input file whith this data? If you are reading from excel file send it .xlsx as well.

13

Change your “DateCheck” variable type. Use

DateCheck = Datetime.Now As Datetime type

14

If I change the datecheck to datetime then I cant change the format todays date to match the imported data

15

image
image1124×188 4.53 KB

16

image
image1212×234 5.94 KB

The data gets saved into the datatable in the same format as displayed in the excel column

I cant send the spreadsheet as its on a remote machine

17

Use this if date check will always be Today’s date

(From r in dt_test.Select()
	Where String.IsNullOrEmpty(r("Closing Date").toString) AndAlso Datetime.ParseExact(r("Closing Date").toString,“dd/MM/yyyy”,System.Globalization.CultureInfo.InvariantCulture) < Datetime.Now.AddHours(-DateTime.Now.Hour).AddMinutes(-Datetime.Now.Minute).AddSeconds(-Datetime.Now.Second)
	Select r).CopyToDatatable
18

Or you can use

(From r in dt_test.Select()
	Where String.IsNullOrEmpty(r("Closing Date").toString) AndAlso Datetime.ParseExact(r("Closing Date").toString,“dd/MM/yyyy”,System.Globalization.CultureInfo.InvariantCulture) < Datetime.ParseExact(DateCheck,“dd/MM/yyyy”,System.Globalization.CultureInfo.InvariantCulture) 
	Select r).CopyToDatatable
19

Using the 1st example - DateTime Now declared or within scope

20

Sorry Not Declared or within scope