RadGrid DateTime Filtering

[Michael O'Flaherty]

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 + "')";

            }

[Michael O'Flaherty]

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;
            }
        }

[Michael O'Flaherty]

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

[Michael O'Flaherty]

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);
                    }
                }
            }