Convert and Filter Data Table

[Kytyzow]

Hello all,
I have a Data Table with several columns. All columns are formatted as string. One column has a date as content.

Now I want to compare for this column per row with a date. Since the column is a string this does not work.

I am using the filter Data Table activity.

Are there any suggestions how to solve it?

[ppr (Peter Preuss)]

find starter help here:
DT_FilterDates_Yesterday.xaml (8.3 KB)

[Yoichi]

Hi,

If column “POSTEING_ANFRA…” is String type, can you try to remove Cint like Now.AddDays(-14).ToString("yyyyMMdd") in Value field?

Regards,

[Jeroen_van_Loon]

I would alter the datatable and use a DateTime datatype for the date column. This allows for direct comparison with Now.AddDays(-14).

A good practice is to keep data formats as close to reality as possible. Using integers…? store them as integer. Same with dates.
And the least you should do is compare apples to apples. Converting a date to an integer (using cInt ), and then checking if it is smaller or larger than a string value will yield… unexpected results.
Only cast values to strings when you need to actually transform them to text, like log messages, or tying into etc.

Converting from any type into a string is usually a lot easier and clearer than the other way around.

[Kytyzow]

Unfortunately, I have no control over the initial table.
The date is stored in the initial table in the format yyyyMMdd as a string.

First it must be converted to the format date and then filtering must take place. It doesn’t matter if the conversion and the filtering happen in one step or not.

How can I convert a whole column, without the header row, from String to Date?

I have tried that, unfortunately without success.

(From row In varDtInitial.Select()
Where CDate(row("POSTEING_QUEST"))>Now
Select row).CopyToDataTable

[ppr (Peter Preuss)]

as showcased in the provided xaml with the following modifications:

(From row In varDtInitial.AsEnumerable
Where CDate(row("POSTEING_QUEST").toString.Trim).Date>Now.AddDays(-14).Date
Select row).CopyToDataTable

in case of empty result we do handle it defensive with following pattern:

in case of the datetime string is within another not out of the box parseable, we can shift to the DateTime.parseExact method

[Kytyzow]

I get an error message.

Multiple Assign: Can not assign ‘(From row In varDtInitial.AsEnumerable
Where CDate(row(“POSTEING_ANFRAGE”).toString.Trim).Date>Now.AddDays(-14).Date
Select row).CopyToDataTable’ to ‘varDtGefiltert’.

[ppr (Peter Preuss)]

can you please share a screenshot from the implementation. Also we would recommend to doi within a single assign activity

Assign Activity:
LHS: YourResultDT| datatype: DataTable
RHS:

(From row In varDtInitial.AsEnumerable
Where CDate(row("POSTEING_QUEST").toString.Trim).Date>Now.AddDays(-14).Date
Select row).CopyToDataTable

it is validating as shown here:

[Kytyzow]

I start with a SQL query and write the result to varDtInitial.

With the UiPath activity Filter Data Table invalid dates (00000000) are filtered and written into the varDtInitial.

After that the try to filter only the last 14 days and write it to the varDtFiltered.

Result: Assign: Conversion from string “20150731” to type ‘Date’ is not valid.

[ppr (Peter Preuss)]

@Kytyzow

Result: Assign: Conversion from string “20150731” to type ‘Date’ is not valid.

was mentioned / handled by:

@ppr

in case of the datetime string is within another not out of the box parseable, we can shift to the DateTime.parseExact method

you can do the following for handling the 00000000 rows like

(From d In varDtInitial.AsEnumerable
Let chk1 = DateTime.TryParseExact(d("POSTEING_QUEST").toString.Trim,"yyyyMMdd", CultureInfo.InvariantCulture,DateTimeStyles.None, Nothing)
Let chk2 = If(chk1, DateTime.ParseExact(d("POSTEING_QUEST").toString.Trim,"yyyyMMdd", CultureInfo.InvariantCulture).Date > Now.AddDays(-14), True)
Where chk2
Select r = d).CopyToDataTable

it will keep the rows with nonparseable dates and rows with the date greater than minus 14 days back

ensure following:

just incorporate also the empty result pattern optional, when needed

BTW: if SQL is used for retrieving the data, then working as close as pssible on the source is a preferred option. Check also to do the filtering directly within the SQL statement