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

RadGrid DateTime Filtering

3 Answers 153 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Michael O'Flaherty
Top achievements
Rank 2
Michael O'Flaherty asked on 10 Apr 2012, 06:52 PM
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 + "')";
 
            }


3 Answers, 1 is accepted

Sort by
0
Michael O'Flaherty
Top achievements
Rank 2
answered on 11 Apr 2012, 03:02 PM
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;
            }
        }

0
Michael O'Flaherty
Top achievements
Rank 2
answered on 11 Apr 2012, 03:59 PM
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
0
Michael O'Flaherty
Top achievements
Rank 2
answered on 12 Apr 2012, 02:22 PM
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);
                    }
                }
            }
Tags
Grid
Asked by
Michael O'Flaherty
Top achievements
Rank 2
Answers by
Michael O'Flaherty
Top achievements
Rank 2
Share this question
or