Rad Grid date Picker Filter not working

[Manishkumar]

HI,
 We have Date Filter and Text Filter Columns, When we apply only Date filter it's filtering the records properly,
But when we trying to apply the Date filter  with  any other text filters then its igoring the date filter and its not persisting the current value of Date filter.
E.g.
Suppose we have three filters F1, F2 (text filters) and F3 is date filter.
Now for the first time when we apply F3 with F1, the records are filtered properly but when we apply filter F2  in addition to previous filters then its ignoring the F3 and it is  not persisting the current value of F3. The records are filtered based on F1 and F2 filter even if there is F3 filter applied.

[Eyup]

Hello Manishkumar,

I have created a sample RadGrid web site to test the described behavior. On my side everything works as expected and the DateTime column successfully persists its filtered state. Please check out the attached application and try to distinguish the crucial differences between our projects.

Greetings,
Eyup
the Telerik team

[Manishkumar]

The format in which I am displaying date field is "MM/DD/YYYY HH24:MI:SS" but while filtering i have to ignore time. Now when i select a date and filter option as "Equal to", it doesn't works.
I have also used EnableTimeIndependentFiltering ="true" but it didn't work.

For instance, grid has a date as "11/01/2012 17:28:19". When I select this date from date picker(date is displayed as "11/1/2012" on filter textbox) and apply "Equal to" filter it displays "No records found" despite of having records for this selected date. I tried handling this from code behind but then the problem persits which i have mentioned above. I bring two fields from database date1 with format "MM/DD/YYYY HH24:MI:SS" to display on grid and date1_filter with format "MM/DD/YYYY" which i dont dislpay but use for filtering but doing this doesn't persists filter value for date picker.
Below is the code:

protected void grid1_ItemCommand(object source, GridCommandEventArgs e)  

{

try

{

if (e.CommandName == RadGrid.FilterCommandName)
{

Pair filterPair = e.CommandArgument as Pair; 
string filterBy = (((System.Web.UI.Pair)(e.CommandArgument))).First.ToString();
string columnName = Convert.ToString(filterPair.Second).ToUpper();
string date = string.Empty;

DateTime startDate = new DateTime();

if (columnName == "date1")

{
e.Canceled = true; 

if ((((RadDatePicker)((GridFilteringItem)e.Item)[Convert.ToString(columnName)].Controls[0])).DbSelectedDate != null)

date = (((RadDatePicker)((GridFilteringItem)e.Item)[Convert.ToString(columnName)].Controls[0])).DbSelectedDate.ToString();
else

date = string.Empty;

columnName = columnName + "_FILTER"; 

if (date != null && date != string.Empty)

{

date = date.Split(' ')[0];

startDate = Convert.ToDateTime(date);
}

string newFilter = string.Empty;

switch (filterBy) 

{
case "EqualTo":
newFilter = "([" + columnName + "] = '" + startDate.ToString("MM/dd/yyyy") + "')";
break;
case "NoFilter":
newFilter =
string.Empty;
break;
case "NotEqualTo":
newFilter = "([" + columnName + "] <> '" + startDate.ToString("MM/dd/yyyy") + "')";
break;
case "GreaterThan":
newFilter = "([" + columnName + "] > '" + startDate.ToString("MM/dd/yyyy") + "')";
break;
case "LessThan":
newFilter ="([" + columnName + "] < '" + startDate.ToString("MM/dd/yyyy") + "')";
break;
case "LessThanOrEqualTo":
newFilter = "([" + columnName + "] <= '" + startDate.ToString("MM/dd/yyyy") + "')";
break; 
case "GreaterThanOrEqualTo":
newFilter = "([" + columnName + "] >= '" + startDate.ToString("MM/dd/yyyy") + "')"; 
break;
case "IsNull":
newFilter = "([" + columnName + "] IS NULL)";
break;
case "NotIsNull":
newFilter = "(NOT ([" + columnName + "] IS NULL))"; 
break;
default:
newFilter = string.Empty;
break;
}

GridBoundColumn dateColumn = (GridBoundColumn)e.Item.OwnerTableView.GetColumnSafe(columnName);
 
if (!date.Equals(string.Empty))
dateColumn.CurrentFilterValue = startDate.ToString("MM/dd/yyyy");

if (grid1.MasterTableView.FilterExpression != "")
{

if (newFilter != "")

grid1.MasterTableView.FilterExpression = grid1.MasterTableView.FilterExpression + " AND " + newFilter;
}

else 
{
grid1.MasterTableView.FilterExpression = newFilter;

}

grid1.Rebind();
}

 

[Manishkumar]

Please provide the solution asap.

[Eyup]

Hi Manishkumar,

Please try the following approach:

protected void RadGrid1_ItemCommand(object sender, GridCommandEventArgs e)
{
    if (e.CommandName == RadGrid.FilterCommandName)
    {
        e.Canceled = true;
        // your code
        GridColumn column = RadGrid1.MasterTableView.GetColumnSafe("DateColumn") as GridColumn;
        column.CurrentFilterFunction = GridKnownFunction.EqualTo;
        column.CurrentFilterValue = "SomeDate";
        RadGrid1.MasterTableView.FilterExpression = column.EvaluateFilterExpression();
        RadGrid1.MasterTableView.Rebind();
    }
}

I hope this will prove helpful. Please give it a try and let me know about the result.

Regards,
Eyup
the Telerik team

[Manishkumar]

Solution which u provided is not working. Getting error in EvaluateFilterExpression. I have one date column with Time (mm/dd/yyyy HH:MM:SS) , as filter with datepicker is not working for this format
so have added second date column ( which is visible=false in grid)with format (mm/dd/yyyy) ignoring time. So to handle filter on this column i have added below code, which filter the record by using second date column.
below are two date columns :
Last Accessed with (mm/dd/yyyy HH:MM:SS) format 
last_accessed_filter (mm/dd/yyyy) format
 <Columns>
 <telerik:GridDateTimeColumn DataField="last_accessed" HeaderText="Last Accessed"
                                    SortExpression="last_accessed" UniqueName="last_accessed" PickerType="DatePicker"
                                    ItemStyle-HorizontalAlign="Left" HeaderStyle-HorizontalAlign="Center" HeaderStyle-Width="150px"
                                    ItemStyle-Width="150px" FilterControlWidth="110px" FilterListOptions="AllowAllFilters"
                                    AllowFiltering="true" DataFormatString="{0:MM/dd/yyyy}" AllowSorting="true" Visible="true"
                                    EnableTimeIndependentFiltering="true">
                                    <ItemStyle Font-Size="8pt" HorizontalAlign="Left" Width="150px" />
                                    <HeaderStyle Font-Size="8pt" HorizontalAlign="Center" Width="150px" />
                                </telerik:GridDateTimeColumn>
                            </Columns>
                            <Columns>
                                <telerik:GridDateTimeColumn DataField="last_accessed_filter" HeaderText="Last Accessed_filter"
                                    Visible="false" SortExpression="last_accessed_filter" UniqueName="last_accessed_filter"
                                    PickerType="DatePicker" ItemStyle-HorizontalAlign="Left" HeaderStyle-HorizontalAlign="Center"
                                    DataFormatString="{0:MM/dd/yyyy}" HeaderStyle-Width="150px" ItemStyle-Width="150px"
                                    FilterControlWidth="120px" FilterListOptions="AllowAllFilters" AllowFiltering="true"
                                    DataType="System.DateTime" AllowSorting="true">
                                    <ItemStyle Font-Size="8pt" HorizontalAlign="Left" Width="150px" />
                                    <HeaderStyle Font-Size="8pt" HorizontalAlign="Center" Width="150px" />
                                </telerik:GridDateTimeColumn>
</Columns>
Below is the code:

protected void grid1_ItemCommand(object source, GridCommandEventArgs e)  

{

try

{

if (e.CommandName == RadGrid.FilterCommandName)
{

Pair filterPair = e.CommandArgument as Pair; 
string filterBy = (((System.Web.UI.Pair)(e.CommandArgument))).First.ToString();
string columnName = Convert.ToString(filterPair.Second).ToUpper();
string date = string.Empty;

DateTime startDate = new DateTime();

if (columnName == "date1")

{
e.Canceled = true; 

if ((((RadDatePicker)((GridFilteringItem)e.Item)[Convert.ToString(columnName)].Controls[0])).DbSelectedDate != null)

date = (((RadDatePicker)((GridFilteringItem)e.Item)[Convert.ToString(columnName)].Controls[0])).DbSelectedDate.ToString();
else

date = string.Empty;

columnName = columnName + "_FILTER"; 

if (date != null && date != string.Empty)

{

date = date.Split(' ')[0];

startDate = Convert.ToDateTime(date);
}

string newFilter = string.Empty;

switch (filterBy) 

{
case "EqualTo":
newFilter = "([" + columnName + "] = '" + startDate.ToString("MM/dd/yyyy") + "')";
break;
case "NoFilter":
newFilter =
string.Empty;
break;
case "NotEqualTo":
newFilter = "([" + columnName + "] <> '" + startDate.ToString("MM/dd/yyyy") + "')";
break;
case "GreaterThan":
newFilter = "([" + columnName + "] > '" + startDate.ToString("MM/dd/yyyy") + "')";
break;
case "LessThan":
newFilter ="([" + columnName + "] < '" + startDate.ToString("MM/dd/yyyy") + "')";
break;
case "LessThanOrEqualTo":
newFilter = "([" + columnName + "] <= '" + startDate.ToString("MM/dd/yyyy") + "')";
break; 
case "GreaterThanOrEqualTo":
newFilter = "([" + columnName + "] >= '" + startDate.ToString("MM/dd/yyyy") + "')"; 
break;
case "IsNull":
newFilter = "([" + columnName + "] IS NULL)";
break;
case "NotIsNull":
newFilter = "(NOT ([" + columnName + "] IS NULL))"; 
break;
default:
newFilter = string.Empty;
break;
}

GridBoundColumn dateColumn = (GridBoundColumn)e.Item.OwnerTableView.GetColumnSafe(columnName);
 
if (!date.Equals(string.Empty))
dateColumn.CurrentFilterValue = startDate.ToString("MM/dd/yyyy");

if (grid1.MasterTableView.FilterExpression != "")
{

if (newFilter != "")

grid1.MasterTableView.FilterExpression = grid1.MasterTableView.FilterExpression + " AND " + newFilter;
}

else 
{
grid1.MasterTableView.FilterExpression = newFilter;

}

grid1.Rebind();
}

Then filter for date is working but  when i applied other filter with this date filter then its ignoring the date column filter and records get filtered with only with the second filter column. The sample code which you provided didn't work for "Equal to" filter.

[Eyup]

Hello Manishkumar,

In the suggested approach, DateColumn represents the invisible column. It works as expected on my side.

In order to reproduce the issue, please open a support ticket and provide a sample runnable application demonstrating the problematic behavior. Thus, we will be able to further analyze the project and provide a proper solution.

All the best,
Eyup
the Telerik team