This is a migrated thread and some comments may be shown as answers.

GridDateTimeColumn Equal filter problem

10 Answers 340 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Bertrand Dessaintes
Top achievements
Rank 1
Bertrand Dessaintes asked on 06 Feb 2009, 05:05 PM
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 ?

 

10 Answers, 1 is accepted

Sort by
0
Georgi Krustev
Telerik team
answered on 10 Feb 2009, 11:12 AM
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.
0
Maxim Tairov
Top achievements
Rank 1
answered on 03 Jun 2010, 10:05 AM
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
0
Maxim Tairov
Top achievements
Rank 1
answered on 04 Jun 2010, 12:50 PM

I have solved described problem, but still ned help! 

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

0
Mira
Telerik team
answered on 08 Jun 2010, 01:49 PM
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.
0
July
Top achievements
Rank 2
answered on 07 May 2012, 02:59 PM
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
0
Antonio Stoilkov
Telerik team
answered on 10 May 2012, 11:33 AM
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.

Kind regards,
Antonio Stoilkov
the Telerik team
If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
0
Pothukuchi
Top achievements
Rank 1
answered on 12 Oct 2012, 12:53 PM
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
0
Vasil
Telerik team
answered on 16 Oct 2012, 01:24 PM
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
If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
0
Pothukuchi
Top achievements
Rank 1
answered on 17 Oct 2012, 05:59 AM
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
0
Vasil
Telerik team
answered on 17 Oct 2012, 08:59 AM
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
If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
Tags
Grid
Asked by
Bertrand Dessaintes
Top achievements
Rank 1
Answers by
Georgi Krustev
Telerik team
Maxim Tairov
Top achievements
Rank 1
Mira
Telerik team
July
Top achievements
Rank 2
Antonio Stoilkov
Telerik team
Pothukuchi
Top achievements
Rank 1
Vasil
Telerik team
Share this question
or