RadGrid DateTime Filtering

4 posts, 0 answers
  1. Michael O'Flaherty
    Michael O'Flaherty avatar
    67 posts
    Member since:
    Jun 2010

    Posted 10 Apr 2012 Link to this post

    Hi!

    We have a grid that contains a date-time column where we are displaying hours and minutes. We want to use only the date portion of the selected value for our filter, and on the SQL side of this, we are able to parse the value using the C# code below.

    The problem we are seeing is that when we stick the data back into the grid, no rows are showing. We suspect the grid is filtering the data still on the date-time value as designated in the aspx code. Is there a way to accomplish this?

    Thanks!

    <telerik:GridDateTimeColumn DataField="AssignDate" DataFormatString="{0:MM/dd/yyyy hh:mm tt}"
                                    HeaderText="Assign Date" PickerType="DatePicker" SortExpression="AssignDate"
                                    UniqueName="AssignDate" />
     
     
     
     
    if (filter.Contains("[AssignDate]"))
                {
                    int index = filter.IndexOf("([AssignDate]");
                    int firstQuote = filter.IndexOf("'", index);
                    int lastQuote = filter.IndexOf("'", firstQuote + 1);
     
                    string stringToExcise = filter.Substring(index, lastQuote - index + 2);
                    string dateToParse = filter.Substring(firstQuote + 1, lastQuote - firstQuote - 1);
                    DateTime dateTime = DateTime.Parse(dateToParse);
     
                    assignDate = "(convert(varchar(10), [AssignDate], 101) = '" + dateTime.Month.ToString("0#") + "/" + dateTime.Day.ToString("0#") + "/" + dateTime.Year + "')";
     
                }


  2. Michael O'Flaherty
    Michael O'Flaherty avatar
    67 posts
    Member since:
    Jun 2010

    Posted 11 Apr 2012 Link to this post

    Hopefully this helps someone. Based on this thread http://www.telerik.com/community/forums/aspnet-ajax/grid/adding-a-custom-filter-function.aspx , I needed to intercept the grid's built-in filtering as well:

    protected void radGridReferrals_ItemCommand(object source, GridCommandEventArgs e)
            {
                if (e.CommandName == RadGrid.FilterCommandName)
                {
                    // this code is necessary to handle date-time filters where we want to show hh:mm in the grid
                    // but want the filter to be loose in the sql (m/d/y.)
                    Pair filterPair = (Pair)e.CommandArgument;
     
                    if ((filterPair.Second.ToString() == "AssignDate") || (filterPair.Second.ToString() == "ReferralDate"))
                        e.Canceled = true;
                }
            }

  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Michael O'Flaherty
    Michael O'Flaherty avatar
    67 posts
    Member since:
    Jun 2010

    Posted 11 Apr 2012 Link to this post

    Better yet, it does appear that that grid honors an SQL override. I have attached the solution. We are still testing, but this appears to work:

    protected void radGridReferrals_NeedDataSource(object sender, GridNeedDataSourceEventArgs e)
            {
                string filterExpression = SoftenDateFilter(radGridReferrals.MasterTableView.FilterExpression);
                radGridReferrals.MasterTableView.FilterExpression = filterExpression;
     
                AssignmentDb assignmentDb = new AssignmentDb();
     
                if ((this.TotalCount == 0) || (filterExpression != this.Filter))
                {
                    int totalCount = assignmentDb.GetReferralsForAssignmentCount(filterExpression);
                    radGridReferrals.VirtualItemCount = totalCount;
                    this.TotalCount = totalCount;
                    this.Filter = filterExpression;
                }
     
                int startRowIndex = radGridReferrals.CurrentPageIndex * radGridReferrals.PageSize;
                int maximumRows = radGridReferrals.PageSize;
                string sortExpression = GetRadGridSortExpression();
     
                radGridReferrals.DataSource = assignmentDb.GetReferralsForAssignment(startRowIndex, maximumRows, sortExpression, filterExpression);
            }
     
    #region SoftenDateFilter
     
            private string SoftenDateFilter(string filter)
            {
                // for columns [AssignDate] and [ReferralDate], we need some help. We need to ignore the time element in the filter query
                string ret = filter;
                string assignDate = string.Empty;
                string referralDate = string.Empty;
     
                if (filter.Contains("([AssignDate]"))
                {
                    int index = filter.IndexOf("([AssignDate]");
                    int firstQuote = filter.IndexOf("'", index);
                    int lastQuote = filter.IndexOf("'", firstQuote + 1);
     
                    string stringToExcise = filter.Substring(index, lastQuote - index + 2);
                    string dateToParse = filter.Substring(firstQuote + 1, lastQuote - firstQuote - 1);
                    DateTime dateTime = DateTime.Parse(dateToParse);
     
                    assignDate = "(convert(varchar(10), [AssignDate], 101) = '" + dateTime.Month.ToString("0#") + "/" + dateTime.Day.ToString("0#") + "/" + dateTime.Year + "')";
     
                    ret = ret.Replace(stringToExcise, assignDate);
                }
     
                if (filter.Contains("([ReferralDate]"))
                {
                    int index = filter.IndexOf("([ReferralDate]");
                    int firstQuote = filter.IndexOf("'", index);
                    int lastQuote = filter.IndexOf("'", firstQuote + 1);
     
                    string stringToExcise = filter.Substring(index, lastQuote - index + 2);
                    string dateToParse = filter.Substring(firstQuote + 1, lastQuote - firstQuote - 1);
                    DateTime dateTime = DateTime.Parse(dateToParse);
     
                    referralDate = "(convert(varchar(10), [ReferralDate], 101) = '" + dateTime.Month.ToString("0#") + "/" + dateTime.Day.ToString("0#") + "/" + dateTime.Year + "')";
     
                    ret = ret.Replace(stringToExcise, referralDate);
                }
     
                return ret;
            }
     
            #endregion
  5. Michael O'Flaherty
    Michael O'Flaherty avatar
    67 posts
    Member since:
    Jun 2010

    Posted 12 Apr 2012 Link to this post

    Updated. Hacked together but functional:

    // for columns [AssignDate] and [ReferralDate], we need some help. We need to ignore the time element in the filter query
                string ret = filter;
                string assignDate = string.Empty;
                string referralDate = string.Empty;
     
                if (filter.Contains("([AssignDate]"))
                {
                    int index = filter.IndexOf("([AssignDate]");
                    int firstQuote = filter.IndexOf("'", index);
                    int lastQuote = filter.IndexOf("'", firstQuote + 1);
     
                    if ((firstQuote > -1) && (lastQuote > -1))
                    {
                        string stringToExcise = filter.Substring(index, lastQuote - index + 2);
     
                        if ((stringToExcise.Contains("IS NULL") == false) && (stringToExcise.Contains("IS NOT NULL") == false))
                        {
                            string dateToParse = filter.Substring(firstQuote + 1, lastQuote - firstQuote - 1);
                            DateTime dateTime = DateTime.Parse(dateToParse);
     
                            string filterModifier = string.Empty;
                            if (stringToExcise.Contains("<>"))
                                filterModifier = "<>";
                            else if (stringToExcise.Contains(">="))
                                filterModifier = ">=";
                            else if (stringToExcise.Contains("<="))
                                filterModifier = "<=";
                            else if (stringToExcise.Contains(">"))
                                filterModifier = ">";
                            else if (stringToExcise.Contains("<"))
                                filterModifier = "<";
                            else
                                filterModifier = "=";
     
                            assignDate = "(convert(varchar(10), [AssignDate], 101) " + filterModifier + " '" + dateTime.Month.ToString("0#") + "/" + dateTime.Day.ToString("0#") + "/" + dateTime.Year + "')";
     
                            ret = ret.Replace(stringToExcise, assignDate);
                        }
                    }
                }
Back to Top