GridDateTimeColumn Equal filter problem

[Bertrand Dessaintes]

Hello,

I have a radgrid with GridDateTimeColumn

<

 

telerik:GridDateTimeColumn DataField="Date_input" HeaderText="Date"

 

 

UniqueName="Date_input" ReadOnly ="true"

 

 

DataType="System.DateTime">

 

 

<ItemStyle Width="130px" />

 

 

</telerik:GridDateTimeColumn>

OR

 

<

 

telerik:GridDateTimeColumn DataField="Date_input" HeaderText="Date"

 

 

UniqueName="Date_input" ReadOnly ="true"

 

 

DataFormatString="{0:dd/MM/yyyy}" DataType="System.DateTime">

 

 

 

 

 

<ItemStyle Width="130px" />

 

 

 

 

 

</telerik:GridDateTimeColumn>

In my MSSQL database it is a datetime value ex : 20/01/2009 11:20:10

My problem it is when a put exat date value in filter field, only date ... that don't work... per exemple : 20/01/2009, my row with 20/01/2009 11:20:10, not display ...

i try http://www.telerik.com/help/aspnet-ajax/grdfilteringfordatetimecolumnwithdataformatstring.html for other date format but i is not ... that work when i make greatthan filter .... i suppose that 20/01/2009 11:20:10 is not same than 20/01/2009 , it is correct in fact ...

Do you have a solution for have results with 20/01/2009 ?

 

[Georgi Krustev]

Hi Bertrand,

To achieve your goal, you can try to modify the filter pattern in a way to return collection which has the same Date, but different time part. You can attain this as you modify, for instance, the "EqualTo" filter pattern like that:

...
                case "EqualTo":
                    var dateTime = Convert.ToDateTime(filterPattern);
                    filterPattern = String.Format("[OrderDate] > '{0}' AND [OrderDate] < '{1}'", filterPattern, dateTime.AddDays(1).ToShortDateString());
                    dateColumn.CurrentFilterFunction = GridKnownFunction.EqualTo;
                    break;
...

This code is modified part from the help topic, which you point to in your previous post.

The other solution is to get the filter text, and current filter pattern and with this data to filter the provided data source against them. This can be done when NeedDataSource is raised. In it you can put already filtered data, and in case there is no filter criteria applied, you can provide not filtered data source.

Best regards,
Georgi Krustev
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

[Maxim Tairov]

Hello Telerik team. I am using latest version of Telerik.Web.UI library (2010.1.519.35). I have the same problem with internal (basic) filter in GridDateTimeColumn. All columns in my grid is autogenerated. NeedDataSource  handler is using for getting DataTable. Data source - SQL Server 2008. When I try to set "EqualTo" for date field - I have got empty datasource. I tried to implement described solution (hook "Filter" command), but during binding I have got an Exception "Expression expected". How can I fix filtering problem

[Maxim Tairov]

I have solved described problem, but still ned help! 

Expression NotEqualTo doesn't work!!! What kind of expression I can set for NotEqualTo?

[Mira]

Hello Maxim,

Please try using the following code to implement the desired functionality:

case "NotEqualTo":
    var dateTime = Convert.ToDateTime(filterPattern);
    filterPattern = String.Format("[OrderDate] < '{0}' OR [OrderDate] > '{1}'", filterPattern, dateTime.AddDays(1).ToShortDateString());
    dateColumn.CurrentFilterFunction = GridKnownFunction.NotEqualTo;
    break;

I hope this helps.

All the best,
Mira
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[July]

I have the same scenario, but when apply other filter custom date filter losted.
steps:
1- filter by date --> filter is OK.
2- filter by date, filter by colum x--> filter does not work because lost custom date filter.

This is my code:
 public static void FilterDateTimes(RadGrid grid, GridCommandEventArgs e, string columnName, string actionFilter)
        {
            e.Canceled = true;
            var dateColumn = (GridBoundColumn)e.Item.OwnerTableView.GetColumnSafe(columnName);
            string newFilter;

            switch (actionFilter)
            {
                case "NoFilter":
                    string removeFilter;
                    if (HttpContext.Current.Session["equalTo"] != null)
                    {
                        removeFilter = HttpContext.Current.Session["equalTo"].ToString();

                        if (grid.MasterTableView.FilterExpression.Contains("AND" + removeFilter))
                            grid.MasterTableView.FilterExpression =
                                grid.MasterTableView.FilterExpression.Replace("AND" + removeFilter, "");

                        if (grid.MasterTableView.FilterExpression.Contains(removeFilter))
                            grid.MasterTableView.FilterExpression =
                                grid.MasterTableView.FilterExpression.Replace(removeFilter, "");
                    }

                    if (HttpContext.Current.Session["NoEqualTo"] != null)
                    {
                        removeFilter = HttpContext.Current.Session["NoEqualTo"].ToString();

                        if (grid.MasterTableView.FilterExpression.Contains("AND" + removeFilter))
                            grid.MasterTableView.FilterExpression =
                                grid.MasterTableView.FilterExpression.Replace("AND" + removeFilter, "");

                        if (grid.MasterTableView.FilterExpression.Contains(removeFilter))
                            grid.MasterTableView.FilterExpression =
                                grid.MasterTableView.FilterExpression.Replace(removeFilter, "");
                    }

                  

                    dateColumn.CurrentFilterFunction = GridKnownFunction.NoFilter;
                    dateColumn.CurrentFilterValue = null;

                    break;

                case "EqualTo":


                    var dt =
                        ((RadDateInput)
                         ((GridFilteringItem)e.Item)[Convert.ToString(columnName)].Controls[1])
                        ;

                    if (dt.Text == null) return ;
                    DateTime date = Convert.ToDateTime(dt.SelectedDate);
                    DateTime startDate = Convert.ToDateTime(date);
                    DateTime endDate = Convert.ToDateTime(date).AddDays(1);

                    newFilter = "('" + startDate.ToString("G") + "' < [Date] AND [Date] < '" +
                                       endDate.ToString("G") + "')";

                     if (HttpContext.Current.Session["equalTo"] != null)
                     {
                         removeFilter = HttpContext.Current.Session["equalTo"].ToString();

                         if (grid.MasterTableView.FilterExpression.Contains("AND" + removeFilter))
                             grid.MasterTableView.FilterExpression =
                                 grid.MasterTableView.FilterExpression.Replace("AND" + removeFilter, "");

                         if (grid.MasterTableView.FilterExpression.Contains(removeFilter))
                             grid.MasterTableView.FilterExpression =
                                 grid.MasterTableView.FilterExpression.Replace(removeFilter, "");
                     }

                    HttpContext.Current.Session["equalTo"] = newFilter;

                    if (grid.MasterTableView.FilterExpression == "")
                    {
                        grid.MasterTableView.FilterExpression = newFilter;
                    }
                    else
                    {
                        if (HttpContext.Current.Session["NoEqualTo"] != null)
                        {
                            removeFilter = HttpContext.Current.Session["NoEqualTo"].ToString();

                            if (grid.MasterTableView.FilterExpression.Contains("AND" + removeFilter))
                                grid.MasterTableView.FilterExpression =
                                    grid.MasterTableView.FilterExpression.Replace("AND" +  removeFilter, "");
                           
                            if (grid.MasterTableView.FilterExpression.Contains(removeFilter))
                                grid.MasterTableView.FilterExpression =
                                    grid.MasterTableView.FilterExpression.Replace(removeFilter, "");
                            
                        }

                        if (grid.MasterTableView.FilterExpression == "")
                        {
                            grid.MasterTableView.FilterExpression = newFilter;
                        }
                        else
                        {
                            grid.MasterTableView.FilterExpression += "AND" + newFilter;
                        }

                    }

                    dateColumn.CurrentFilterValue = Convert.ToDateTime(date).ToString("dd/MM/yyyy");
                    dateColumn.CurrentFilterFunction = GridKnownFunction.EqualTo;

                    break;


                case "NotEqualTo":


                    dt =
                        (RadDateInput)
                        ((GridFilteringItem)e.Item)[Convert.ToString(columnName)].Controls[1];

                    if (dt.Text == null) return ;
                    date = Convert.ToDateTime(dt.SelectedDate);

                    startDate = Convert.ToDateTime(date);
                    endDate = Convert.ToDateTime(date).AddHours(24);
                    DateTime beforeDate = Convert.ToDateTime(date).AddMinutes(-1);

                    newFilter = "([Date] <'" + startDate.ToString("G") + "') OR ([Date] >'" + endDate.ToString("G") + "')";

                    if (HttpContext.Current.Session["NoEqualTo"] != null)
                    {
                        removeFilter = HttpContext.Current.Session["NoEqualTo"].ToString();

                        if (grid.MasterTableView.FilterExpression.Contains("AND" + removeFilter))
                            grid.MasterTableView.FilterExpression =
                                grid.MasterTableView.FilterExpression.Replace("AND" + removeFilter, "");
                        if (grid.MasterTableView.FilterExpression.Contains(removeFilter))
                            grid.MasterTableView.FilterExpression =
                                grid.MasterTableView.FilterExpression.Replace(removeFilter, "");

                    }

                    HttpContext.Current.Session["NoEqualTo"] = newFilter;

                    if (grid.MasterTableView.FilterExpression == "")
                    {
                        grid.MasterTableView.FilterExpression = newFilter;
                    }
                    else
                    {
                        if (HttpContext.Current.Session["equalTo"] != null)
                        {
                            removeFilter = HttpContext.Current.Session["equalTo"].ToString();

                            if (grid.MasterTableView.FilterExpression.Contains("AND" + removeFilter))
                                grid.MasterTableView.FilterExpression =
                                    grid.MasterTableView.FilterExpression.Replace("AND" +  removeFilter, "");
                            if (grid.MasterTableView.FilterExpression.Contains(removeFilter))
                                grid.MasterTableView.FilterExpression =
                                    grid.MasterTableView.FilterExpression.Replace(removeFilter, "");
                            
                        }
                        if (grid.MasterTableView.FilterExpression == "")
                        {
                            grid.MasterTableView.FilterExpression = newFilter;
                        }else
                        {
                            grid.MasterTableView.FilterExpression += "AND" + newFilter;
                        }

                        
                       
                    }
                    dateColumn.CurrentFilterFunction = GridKnownFunction.NotEqualTo;

                    dateColumn.CurrentFilterValue = Convert.ToDateTime(date).ToString("G");
                    break;
            }
            
        }


  if (e.CommandName == RadGrid.FilterCommandName)
            {
                var filterPair = e.CommandArgument as Pair;
                string filter = null;

                if (gridSearchHistory.MasterTableView.FilterExpression.StartsWith("AND"))
                   gridSearchHistory.MasterTableView.FilterExpression = gridSearchHistory.MasterTableView.FilterExpression.Remove(0, 3);

                if (Convert.ToString(filterPair.Second).Contains("Date"))
                {
                    WebSearch.Components.CommonFunctions.GridFunction.FilterDateTimes(gridSearchHistory, e,
                                                                                      Convert.ToString(filterPair.Second),
                                                                                      Convert.ToString(filterPair.First));
                                     
                  }

                LoadDataSource();
            }
        }

Screen:



I have a lot of records with 03/05/2012 as date

Steps 1:
filter as 03/05/2012 --> OK



Step 2:
Add Result equal to 4.



Does not work correctly.

in code behind Filter said:
[Date] = '03/05/2012 12:00:00 a.m. --> LOST the custom filter

[Antonio Stoilkov]

Hello Julieta,

We could continue our conversation at the forum thread provided below where you have posted the same questions. Note that we recommend posting your questions at one place because this help us provide our customers with more relevant answers and faster responses.

• http://www.telerik.com/community/forums/aspnet-ajax/grid/griddatetimecolumn---filter-by-date-only---error.aspx

Kind regards,
Antonio Stoilkov
the Telerik team

[Pothukuchi]

Hi Georgi Krustev ,



And am using a Telerik Grid in which filter is working fine in all columns except for the Columns which am fetching Date along with Time..

I have to search based on Date...may I know how can I solve this issue..Will be very thankful to you if I get a solution.


Thanks and Regards,
P.L.Jyotsna

[Vasil]

Hi P.L.Jyotsna,

Did you tried the Antonio's suggestion from this forum thread? It should work in your case with no problem.

Regards,
Vasil
the Telerik team

[Pothukuchi]

Hi Vasil ,
Thanks for the reply.....
I am not using Telerik RadGrid.....
and my data is bounded to grid as

 @{Html.Telerik().Grid(Model)
            .Name("HistoryGrid").HtmlAttributes(new { style = "width:100%" })
            .ToolBar(toolbar => toolbar.Custom().ButtonType(GridButtonType.Image).ImageHtmlAttributes(new { style = " background-image: url('../../Content/images/clear-filter.png') !important" }).HtmlAttributes(new { onclick = "clearFilt(event)", title = "Clear All Filter" }))
            .DataKeys(keys => keys.Add(c => c.AssetID))
            .Columns(columns =>
            {
                columns.Bound(o => o.TableObject).Width(120).Title("Transaction");
                columns.Bound(o => o.TransactionType).Width(120).Title("Transaction Type");
                columns.Bound(o => o.UName).Width(120).Title("Updated by");
                columns.Bound(o => o.ColumnObject).Width(120).Title("Field Name");
            
               columns.Bound(o => o.DateofTransaction).Width(150).ClientTemplate("<#= toServerTime(DateofTransaction) #>").Title("Date of Transaction");
          
                columns.Bound(o => o.OldValue).Width(120);
                columns.Bound(o => o.NewValue).Width(120);
            })
            .DataBinding(binding => binding.Ajax()
                                       .Select("_History", "AssetInfo"))
          

            .Pageable(pager => pager.Style(GridPagerStyles.PageSizeDropDown | GridPagerStyles.NextPreviousAndInput))
            .Filterable(filtering => filtering.Enabled(true))
             .EnableCustomBinding(true)

            .Resizable(resizing => resizing.Columns(true))
            .Sortable(s => s.Enabled(true))
            .ColumnContextMenu()
            .Scrollable(builder => builder.Enabled(true).Height("100%"))
            .Selectable()
            .Render();
        }

and as of now am able to solve my UTC issue when I retrieve TIME along with DATE from Database and it is working fine.......

But now my problem is that the filter on the GRID datetime column is not working.....even though I has given time along with picking up the date from the calender......I need to get the filter by giving only Date, and the filtered values must be read based on Date only even-though, I show the Time in the grid. May I know how to solve the issue as soon as possible....Is there any simple solution I can find by simply changing the Telerik Scripts.... Am new to the usage of this product, please don't mind of giving me the detailed solution of solving this issue......

Thanks and Regards,
Jyotsna

[Vasil]

Hello Jyotsna,

If you ask about the MVC or KendoUI Grid you would get better assistance at the corresponding forums for them.

Regards,
Vasil
the Telerik team