How to Filter data table from specific time given?

Help Studio
, , , ,
1

I have an excel which contains ‘General’ type column called ‘Posting Time’ where the data in “dd-MM-yyyy HH:mm:ss” format. I just need data which posted after 4PM that means to filter out rows where the Posting Time is greater than 16:00:00.

Here I have attached a sample screen shot of my worksheet. Really appreciate your help on this.

image
image809×125 4.74 KB

2

Hi @punya ,

Could you try the Below Linq Query using Assign Activity :

arrayRow = DT.AsEnumerable.Where(Function(x)CDate(x("Posting Time").ToString)>new DateTime(CDate(x("Posting Time").ToString).Year,CDate(x("Posting Time").ToString).Month,CDate(x("Posting Time").ToString).Day,16,0,0)).ToArray

Here, DT is the Datatable variable read from the Excel Sheet using Read Range Activity, arrayRow is a variable of type Array of DataRow.

Follow the Post Below to Convert Array of Rows / List of Rows to Datatable for Handling Exceptions.

3

after 4pm for any date or current date?

4

you can assign this to your datatable variable after you use read range to read excel

dt.AsEnumerable.Where(function(y) DateTime.parseExact(y("Posting Time").ToString, "dd-MM-yyyy HH:mm:ss", nothing).Hour >= 4 ).CopyToDataTable
image

5

Hi @supermanPunch

Thank you so much!

I tried this by changing .ToArray into .CopyToDataTable (Since there was a type mismatching issue from 1-dimentional array assigning into datatable). But this gave me an error saying “Conversion from string “25-04-2022 17:45:24” to type ‘Date’ is not valid.”

6

@jack.chan For any date (Here it is 25-04-2022)

7

@jack.chan Thank you very much for the answer. This worked!!!

But there should be a change as these.

dt.AsEnumerable.Where(function(y) DateTime.parseExact(y(“Posting Time”).ToString, “dd-MM-yyyy HH:mm:ss”, nothing).Hour >= 16 ).CopyToDataTable

8

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