Filter Date range not working

Help Studio
, ,
1

Hi ,

In my excel i have two different columns(Ship 1,Ship 2) with Date values (MM/DD/YYYY) format.
I have used this for Ship 1 “InDT.Select(”[Ship 1] >= ‘#09/05/2021#’ AND [Ship 1] <= ‘#09/28/2021#’“).CopyToDataTable”.It worked without any issues.
For the Ship 2 i have used same code but i am getting error like “The Source contains no DataRows” even though there is a data. I have tried multiple options but didn’t work.
can you help me how to solve this.

1 Like
2

@Sindhu_Penmathsa

Can you check the datatable using output Datatable and using write line / message box activity view the format of the Date which is reading

Hope this may help you

Thanks

3

It’s very simple
If the datatable has records within those two dates for Ship-2 column then this error won’t come

As the filter output has no records it is throwing this error

So before saving it as a database with .CopyToDatatable use a IF condition and then save it

Condition be like this

InDT.Select(“your expression”).length>0

If this bus true it will go to THEN part where can use a assign activity to save the output as a datatable with .CopyToDatatable

Hope this would help you resolve this

Cheers @Sindhu_Penmathsa

4

No That is not my scenario. Even though dates are available its not filtering those values. its giving error message. i can handle exception scenario. You got my point right?

5

I have checked all the options. i did do debugging. i checked type and format. everything looks good. but dates are not filtering…

6

Fine can we a have sample data if possible similar to yours

@Sindhu_Penmathsa

7

For the security reasons cant share my data.

8

Hi @Sindhu_Penmathsa,

Can you try converting the date value read from the Input explicitly to datetime type and then try applying the condition.

Hope this helps!

9

Where do you want me to convert. can you elaborate and if you have sample code. let me know. i will try. Thanks

10

Hi,

The code looks like:
InDT. AsEnumerable.Where(Function(x) DateTime.ParseExact(x(“Ship 2”).ToString.trim, “dd/MM/yyyy”, CultureInfo.InvariantCulture) >= DateTime.ParseExact(“09/05/2021” , “dd/MM/yyyy”, CultureInfo.InvariantCulture)).CopyToDataTable

I have just used only one condition here, you can add multiple And/or as well.

Basically DateTime.ParseExact(“09/05/2021” , “dd/MM/yyyy”, CultureInfo.InvariantCulture) will help us in converting the string value to date time type, but we have to ensure that we specify the correct format as per our input.
In the above example since the date is 09/05/20201 the format used is appropriate I.e., dd/MM/yyyy

Hope this helps, if required I can share the code but will be needing sample date format as per your input.

Regards,
Shivu

11

thanks for the code. i will try and update you.

12

the code is not working. Error ERROR Validation Error Compiler error(s) encountered processing expression “InDT. AsEnumerable.Where(Function(x) DateTime.ParseExact(x(“Ship Commit 2”).ToString.trim, “MM/dd/yyyy”,CultureInfo.InvariantCulture) >= DateTime.ParseExact(“09/05/2021” , “MM/dd/yyyy”,CultureInfo.InvariantCulture)).CopyToDataTable”.
‘CultureInfo’ is not declared. It may be inaccessible due to its protection level.
‘CultureInfo’ is not declared. It may be inaccessible due to its protection level.

13

Can you R
Replace cultureinfo.invariantculture with system.globalization.cultureinfo.invariantculture and try

14

i got this exception “Assign: String was not recognized as a valid DateTime.”

15

The Input value seems to be not in the specified format… Can you try printing the date and check for format?

When you read from an excel file without preserving format the default format may look like MM/dd/yyyy HH:mm:ss.
Note this depends on system settings.

16

It looks like you’re filtering on dates as strings. That’s not going to give you the desired output, you’re treating them as words and alphabetizing, not treating them as actual date values.

You should convert them to proper datetime values and use the Filter Datatable activity to get what you want.

17

Yes. its giving me correct format. MM/dd/yyyy HH:mm::ss.

18

No. those are the date values same code is working for other column on Dates its filtering and everything is good. Only iam having issue with this column only.

20

Then the format should be mentioned appropriately as below
InDT. AsEnumerable.Where(Function(x) DateTime.ParseExact(x(“Ship 2”).ToString.trim, "MM/dd/yyyy HH:mm:ss”,system.globalization.cultureinfo.invariantculture ) >= DateTime.ParseExact(“09/05/2021” , “dd/MM/yyyy”,system.globalization.cultureinfo.invariantculture)).CopyToDataTable