RadGrid filter between dates (+ time)

10 posts, 0 answers
  1. Benny
    Benny avatar
    11 posts
    Member since:
    Jul 2012

    Posted 01 Jul 2010 Link to this post

    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!
  2. Veli
    Admin
    Veli avatar
    2002 posts

    Posted 06 Jul 2010 Link to this post

    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
    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
  3. Scott
    Scott avatar
    11 posts
    Member since:
    Jun 2007

    Posted 28 Jul 2010 Link to this post

    Is this example also available in vb.net.

    Thanks
  4. Veli
    Admin
    Veli avatar
    2002 posts

    Posted 02 Aug 2010 Link to this post

    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
    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
  5. Smiely
    Smiely avatar
    129 posts
    Member since:
    Nov 2010

    Posted 20 Dec 2010 Link to this post

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

    Please help

    Thanks
  6. Smiely
    Smiely avatar
    129 posts
    Member since:
    Nov 2010

    Posted 20 Dec 2010 Link to this post

    Got it working !!
  7. Chris
    Chris avatar
    1 posts
    Member since:
    Nov 2011

    Posted 29 Feb 2012 Link to this post

    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
  8. Marin
    Admin
    Marin avatar
    1057 posts

    Posted 05 Mar 2012 Link to this post

    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
    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.
  9. Jakub
    Jakub avatar
    1 posts
    Member since:
    May 2012

    Posted 19 Sep Link to this post

    Was the issue with using 'Between' with date/times ever addressed?   
  10. Eyup
    Admin
    Eyup avatar
    3296 posts

    Posted 22 Sep Link to this post

    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
    Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
Back to Top