Filter Datatable based of Time Column

[ravig1206 (Ravi Gupta)]

I am working on a automation where I am extracting a table which has 8 columns.
The problem is that I want to filter this datatable besed on time column.
I want only rows which were created 1 hour back.

for example.

In the above screen shot.
the time column has multiple rows and Suppose the current time is 11:00
so bot should keep 7 rows which is starting with 10:xx because it qualifies 1 hour criteria . the time is 24 hour format.
Please help

[Parvathy (PS Parvathy)]

Hi @ravig1206

Try this:

filteredDataTable= (From row In YourDataTableVariable.AsEnumerable()
                                      Let timeStr = row.Field(Of String)("Time")
                                      Let time = DateTime.ParseExact(timeStr, "HH:mm", System.Globalization.CultureInfo.InvariantCulture)
                                      Where time >= DateTime.Now.AddHours(-1)
                                      Select row).CopyToDataTable()

Hope it helps

[Janet_Caroline]

Try/Modify this based on your needs:

SELECT *
FROM your_datatable
WHERE EXTRACT(HOUR FROM your_time_column) = EXTRACT(HOUR FROM CURRENT_TIMESTAMP) - 1;

[Parvathy (PS Parvathy)]

Hi @ravig1206

→ Build Data Table

Output-> dt
→ Use the below syntax in Assign:

filteredDataTable = (From row In dt.AsEnumerable()
                                      Let timeStr = row.Field(Of String)("Time")
                                      Let time = DateTime.ParseExact(timeStr, "HH:mm", System.Globalization.CultureInfo.InvariantCulture)
                                      Where time >= DateTime.Now.AddHours(-1)
                                      Select row).CopyToDataTable()

Hope it helps!!

[pikorpa (Piotr Kołakowski)]

Hey @ravig1206

filteredDt = dtTable.AsEnumerable().Where(Function(row) DateTime.ParseExact(row("Time").ToString(), "HH:mm", System.Globalization.CultureInfo.InvariantCulture) > DateTime.Now.AddHours(-1)).CopyToDataTable()