Select certain time from datetime columns in a datatable

Hi, I’ve been looking for an expression to filter a column in a DataTable (read from excel workbook) based on time, which holds values written in yyyy/MM/dd HH:mm:ss format in each cell. I need to keep the date info, while filtration should be performed focusing on time alone.
for example,

from datarow in dt:

Num DateTime
0 2018/10/01 4:00:00
1 2018/10/01 8:00:00
2 2018/10/01 12:00:00
3 2018/10/01 20:00:00
4 2018/10/02 11:00:00
.
.
.
X 2018/10/31 23:00:00

→select datarow 0 and 1 (<8:00:00) and 3 and X (>20:00:00) and assign to dt_filtered

Since DataTable.Select method does not seem to work with timespan values, i have no idea how to compute this. Maybe in a LINQ expression I can convert Object values to DateTime and to TimeSpan, then finally pick values in a certain time slot?

@Kaoru_Hayashi

You can loop through each record and check-
(new DateTime(2018,2,13,8,0,0).TimeOfDay - Convert.ToDateTime(row(“Time”).ToString).TimeOfDay).TotalSeconds >= 0

I have hard coded the Compare date here DateTime(2018,2,13,8,0,0). You can write code to fetch current date instead.

The same condition check query should work with linq as well with slight changes in syntax.

1 Like

Thank you for the advice!

I suppose you’re suggesting something like this, and it wasn’t as slow as I first thought!:

For each row in DT_log

assign date_time = DateTime.parse(row(“time”).ToString)
assign year = date_time.Year
assign month = date_time.Month
assign day = date_time.Day

If
new DateTime(year,month,day,8,0,0).TimeOfDay
-
Convert.ToDateTime(row(“Time”).ToString).TimeOfDay).TotalSeconds >= 0

OR


(Convert.ToDateTime(row(“Time”).ToString).TimeOfDay
-
new DateTime(year,month,day,20,0,0).TimeOfDay).TotalSeconds >= 0

then
invoke code(ImportRow) row to DT_filtered

Datatable_Filter_TimespanFromDateTime.zip (81.1 KB)

1 Like

Correct