Date Filtering Issue in Filter Data Table Activity

Help Marketplace
,
1

Hi All,

I want to filter specific dates by using “date” >= “2021-01-01” AND “date” <= “2021-12-31”
but the following error received.

“object reference not set to an instance of an object”

When I filter the “date” != “NULL” then, it works fine. Please guide how to handle the date in Filter Data Table.

Many thanks,

2

Hi @Dr_Raza_Abidi_PhD

Try this expression

DtMaster.AsEnumerable.Select (Function(x) x("ColumnNameOrIndex").ToString.Trim >= "2021-01-01" AndAlso x("ColumnNameOrIndex").ToString.Trim <= "2021-12-31").toarray()

Check with the filter DataTable concept

image

Regards
Gokul

3

@Gokul001 : Thanks dear but I have tried the wizard option same as you mentioned above but error occurred. But I did not try with as enumerable option.

Thanks,

4

Can you check the format in the Date column. Just print the date in the message box and check share the screenshot

Regrards
Gokul

5

That’s comparing the dates as strings, which won’t work.

6

These are strings. You don’t compare dates as strings, that won’t work. You have to use datetime.parse or datetime.parseexact on the right side of the filter, and your date column has to be a datetime, not a string.

7

Hi @Dr_Raza_Abidi_PhD,
If you want to filter 2021 year full data and date column is string use like opperation
Dt.Select("date like '2021%'").CopyToDataTable()

in filter data table activity also try the same method to filter the data.

Regards,
Arivu

8

Hi @Gokul001 : Its very strange that I am unable to print the output using write line activity. Pl. help.

image
image598×551 13 KB

9

Hi @Dr_Raza_Abidi_PhD

Try to give column name or Index

image

Regards
Gokul

10

@Gokul001 : Thanks a lot. Below is the format of date column and I only want to filter the rows between the specified date range. The excel I am using is extracted from database and exported into excel. The format of date column in excel is showing General. Pl. guide.

image

11

Hi @Dr_Raza_Abidi_PhD

Try this expression

DtMaster.AsEnumerable.Select (Function(x) x("ColumnNameOrIndex").ToString.Trim >= "2012-03-06" AndAlso x("ColumnNameOrIndex").ToString.Trim <= "2012-03-12").toarray()

Regards
Gokul

12

@Gokul001 : Pl guide how should I use this expression? In the filter Data Table wizard or in the IF condition??

13

In Assign activity @Dr_Raza_Abidi_PhD

14

@Gokul001 : Below is my work flow. Pl. guide how to insert your expression in the Assign activity?

image
image591×610 13.5 KB

15

@postwick : Thanks but the format of my excel date column is General and I cannot change the format in excel because this excel file I have exported from database and in the database date is stored as string. But when I exported the query result in the excel file it shows the format for date column is General. I want to apply filter on this General data type.

16

Create a datetime column in the datatable and then populate it using a For Each, using datetime.parse or datetime.parseexact to convert the string date to an actual datetime. Then use the new column for your comparisons.

1 Like
17

@postwick : Thanks a lot for your suggestion. Issue is resolved now.

Many thanks,

18

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