Filter records based on Time using Dataview Rowfilter

Question

Is it possible to get records between a Timespan from a DataTable using the RowFilter property of the Defaultview that belongs to the table.

Purpose: I would like to check if Current Time(DateTime.Now.ToString()) falls withing the Time defined in the Database table schema. Schema of the table is defined below

                          `Name | From | To | JoinedOn |`

where From To are datetime columns on the database table. I would like to compare only time values from the datetime columns rather than the dates also. I Tried below but doesn't yield the result. dt_Temp.DefaultView.RowFilter = "From < '"+ DateTime.Now.ToString()+"' AND To >'"+ DateTime.Now.ToString()+"'"; anyone have any idea?

Specs:

.Net Framework 2.0

ViSual studio 2005

Sql server express 2005

Magnus Magnus · Accepted Answer · 2011-04-15T08:49:37

I would skip the DefaultView and write a Linq query agains the DataTable instead

var time = DateTime.Now.TimeOfDay;
var q = 
  from row in dt_Temp.AsEnumerable();
  where row.Field<DateTime>("From").TimeOfDay < time && row.Field<DateTime>("To").TimeOfDay  > time
  select row;

Edit Here is a 2.0 compatible function of that query

private static IEnumerable<DataRow> GetRows(DataTable dt_Temp)
{
    TimeSpan time = DateTime.Now.TimeOfDay;
    foreach (DataRow row in dt_Temp.Rows)
        if(((DateTime)row["From"]).TimeOfDay < time && ((DateTime)row["To"]).TimeOfDay > time)
            yield return row;
}

Filter records based on Time using Dataview Rowfilter

2 Answers