RadGrid filter between dates (+ time)

[Benny]

Hi,

I'm trying to filter a grid between two dates.
It works but when the start- and enddate are the same (filter on one date) no results are shown.
The time is also included in the date so I'm guessing I need to adjust my code but I don't know how.

This is my current code:

<telerik:RadScriptBlock ID="scriptFilterEventDate" runat="server">
                                    <script type="text/javascript">
                                        function FromDateSelected(sender, args) {
                                            var tableView = $find("<%# ((GridItem)Container).OwnerTableView.ClientID %>");
                                            var ToPicker = $find('<%# ((GridItem)Container).FindControl("dateTo").ClientID %>');

                                            var fromDate = FormatSelectedDate(sender);
                                            var toDate = FormatSelectedDate(ToPicker);

                                            if (toDate != '') {
                                                tableView.filter("EventDate", fromDate + " " + toDate, "Between");
                                            }
                                        }

                                        function ToDateSelected(sender, args) {
                                            var tableView = $find("<%# ((GridItem)Container).OwnerTableView.ClientID %>");
                                            var FromPicker = $find('<%# ((GridItem)Container).FindControl("dateFrom").ClientID %>');

                                            var fromDate = FormatSelectedDate(FromPicker);
                                            var toDate = FormatSelectedDate(sender);

                                            if (fromDate != '') {
                                                tableView.filter("EventDate", fromDate + " " + toDate, "Between");
                                            }
                                        }

                                        function FormatSelectedDate(picker) {
                                            var date = picker.get_selectedDate();
                                            var dateInput = picker.get_dateInput();
                                            var formattedDate = dateInput.get_dateFormatInfo().FormatDate(date, dateInput.get_displayDateFormat());

                                            return formattedDate;
                                        }
                                    </script>
                                </telerik:RadScriptBlock>
                            </FilterTemplate>

function FormatSelectedDate(picker) {
            var date = picker.get_selectedDate();
            var dateInput = picker.get_dateInput();
            var formattedDate = dateInput.get_dateFormatInfo().FormatDate(date, "dd/MM/yyyy HH:mm:ss");

            return formattedDate;
        }

What should I change?
Thanks in advance!

[Veli]

Hello Benny,

If you need to filter items by one and the same start and end date, make sure the original DateTime values from your data source are equal too. For example, you may want to filter all dates by 7/12/2010, but you also need to make sure there are no time portions different than 12:00 AM.

Sample test page demonstrating this approach is attached.

All the best,
Veli
the Telerik team

[Scott]

Is this example also available in vb.net.

Thanks

[Veli]

Hello Jason,

The only part in C# is the RadGrid databinding logic. You can replace this with any piece of databinding code in VB.NET. The essential part is the javascript RadDatePicker event handlers. Here is the VB.NET equivalent of the RadGrid databinding code:

Protected Sub RadGrid1_NeedDataSource(sender As Object, e As GridNeedDataSourceEventArgs) Handles RadGrid1.NeedDataSource
    RadGrid1.DataSource = SampleData
End Sub

Public ReadOnly Property SampleData() As DataTable
    Get
        If Session("_data") Is Nothing OrElse Not Page.IsPostBack Then
            Dim table As New System.Data.DataTable()
            table.Columns.Add("ID", Type.[GetType]("System.Int32"))
            table.Columns.Add("Name", Type.[GetType]("System.String"))
            table.Columns.Add("Date", Type.[GetType]("System.DateTime"))
            table.Columns.Add("Value", Type.[GetType]("System.Double"))

            Dim rand As New Random()

            For i As Integer = 0 To 9
                table.Rows.Add(i, "Item " + i.ToString(), DateTime.Today.AddDays(-10).AddDays(rand.[Next](20)), rand.NextDouble() * 1000)
            Next

            Session("_data") = table
        End If

        Return DirectCast(Session("_data"), DataTable)
    End Get
End Property

Regards,
Veli
the Telerik team

[Smiely]

Perfect example for me. Thanks Benny. I wanted to add Time to this application. I am using RadDateTimePicker.

Please help

Thanks

[Smiely]

Got it working !!

[Chris]

Hi, and sorry to resurrect such an old thread, but I'm trying to filter on a date range in exactly the same way as the example provided by Veli, except that I want to append the time onto the date.

I would then expect that filtering on 29/02/2012 - 29/02/2012 from the grid filter template datepickers would actually return all records from 29/02/2012 00:00:00 - 29/02/2012 23:59:59.

So all I've done differently to Veli's example is change the code in the RadScriptBlock to the following:

function FromDateSelected(sender, args) {
    var tableView = $find("<%# ((GridItem)Container).OwnerTableView.ClientID %>");
    var ToPicker = $find('<%# ((GridItem)Container).FindControl("ToDatePicker").ClientID %>');

    var fromDate = FormatSelectedFromDate(sender);
    var toDate = FormatSelectedToDate(ToPicker);

    PerformFilter(tableView, fromDate, toDate);
}
function ToDateSelected(sender, args) {
    var tableView = $find("<%# ((GridItem)Container).OwnerTableView.ClientID %>");
    var FromPicker = $find('<%# ((GridItem)Container).FindControl("FromDatePicker").ClientID %>');

    var fromDate = FormatSelectedFromDate(FromPicker);
    var toDate = FormatSelectedToDate(sender);

    PerformFilter(tableView, fromDate, toDate);
}
function FormatSelectedFromDate(picker) {
    var date = picker.get_selectedDate();
    var dateInput = picker.get_dateInput();
    var formattedDate = dateInput.get_dateFormatInfo().FormatDate(date, "dd/MM/yyyy HH:mm:ss");

    return formattedDate;
}
function FormatSelectedToDate(picker) {
    var date = picker.get_selectedDate();
    var dateInput = picker.get_dateInput();
    var formattedDate = dateInput.get_dateFormatInfo().FormatDate(date, "dd/MM/yyyy 23:59:59");

    return formattedDate;
}
function PerformFilter(tableView, fromDate, toDate) {
    if ((fromDate != "") && (toDate != ""))
        tableView.filter("DateField", fromDate + " " + toDate, "Between");
    else if (fromDate != "")
        tableView.filter("DateField", fromDate, "GreaterThanOrEqualTo");
    else if (toDate != "")
        tableView.filter("DateField", toDate, "LessThanOrEqualTo");
    else
        tableView.filter("DateField", fromDate, "NoFilter");
}

This works perfectly when filtering on just From or To or No Filter, but not when both are selected; it simply fails to filter at all.

Substituting the date and time format string with dateInput.get_displayDateFormat() as in Veli's example works perfectly too.

Hope you can help.

Thanks

[Marin]

Hi Chris,

The filtering in this case fails because there is a space in the date format, and the space is also used as a separator between the dates when you pass them as an argument to the filter function.

fromDate1 + " " + toDate //this will contain a total of three spaces

This causes problems when the value is parsed on the server and the separate dates cannot be split properly. We will try to remove this limitation for future releases of the controls. But for now you can handle the between case manually in the ItemCommand event on the server:

protected void RadGrid1_ItemCommand(object sender, GridCommandEventArgs e)
    {
        if (e.CommandName==RadGrid.FilterCommandName)
        {
            var pair = (e.CommandArgument as Pair);
            var filterFunction = pair.First.ToString();
            var columnName = pair.Second.ToString();
            var filterValue = ((e.CommandSource as GridFilteringItem)[columnName].Controls[0] as TextBox).Text;
            //filter datasource
            RadGrid1.DataSource = filteredDataSource;
            RadGrid1.Rebind();
        }
    }

Hope this helps.

Regards,
Marin
the Telerik team

[Jakub]

Was the issue with using 'Between' with date/times ever addressed?   

[Eyup]

Hello Jakub, 

To achieve this requirement you can use the built-in capabilities of RadGrid and replace the template column with a datetime column, along with enabling these settings:

<telerik:GridDateTimeColumn ... EnableRangeFiltering="true" EnableTimeIndependentFiltering="true">

I am also sending a sample web site sample to demonstrate that the suggestion works as expected. Please run the attached application and let me know if it helps you.

Regards,
Eyup
Progress Telerik