Filter GridDateTimeColumn with a time of MM/dd/yyyy hh:mm:ss

[Trevor]

I have a GridDateTimeColumn on my radgrid that is not filtering correctly. When I change the date to be less than a certain date it is not filtering the data correctly. I am assuming it has something to do with the date not being formatted as MM/dd/yyyy and having a time stamp. Here is my code ...

Any help here?

Trevor


<telerik:GridDateTimeColumn HeaderText="Creation Date" DataFormatString="{0:MM/dd/yyyy hh:mm:ss}" SortExpression="Created" ItemStyle-Wrap="false" UniqueName="Created" DataField="whenCreated" FilterListOptions="VaryByDataType" FilterImageUrl="~/images/filter_icon.gif" FilterControlWidth="100px" />

[Jayesh Goyani]

Hello,

Please  check below link.
Filtering for DateTime bound column with DataFormatString different than mm/dd/yyyy

Thanks,
Jayesh Goyani

[Princy]

Hello Trevor,

I tried to replicate the same issue in 2012, 1, 215, 35, but no avail. Filtering worked as expected in my end. Make sure that you use the latest version. Also check the following help documentation which explains filtering with DataFormatString different from mm/dd/yyyy.
Filtering for DateTime Bound Column with DataFormatString Different from mm/dd/yyyy

Thanks,
Princy.

[Trevor]

Thanks for the reply guys. I have searched around before going to the forum and the link you have given me does not do the job. That link is for a regular databoundcolumn that happens to have dates. I am using the GridDateTimeColumn which should have the built in capability to sort a date that is in the format MM/dd/yyyy hh:mm:ss. For example, my dates are listed as 10/2/2012 10:29:23 PM. This is a date that is generated when I create a "DateTime" object in VB.NET. If you create about 10 dates from different years and try to filter the grid by date by clicking on the column header the dates do not sort chronologically as expected. Princy is this what you did when trying to test my case?

Thanks,
Trevor

[Antonio Stoilkov]

Hi Trevor,

In order to set the date time format for a filtering control you could use the FilterDateFormat property of the GridDateTimeColumn. Note that this would not change the filtering behavior. Additionally, it is not expected for the dates to be order chronologically when filtering. In order to achieve such functionality you could enable the sorting for the grid.

Greetings,
Antonio Stoilkov
the Telerik team

[Trevor]

I can not find that property as part of the GridDateTimeColumn. When was this introduced? On a different note when I click on the datecolumn to sort I get the results in my attached image. The dates in there are all datetime objects. Also, I have the code below used for this field in my radgrid.

<telerik:RadGrid ID="gridUsers" DataSourceID="srcUsers" EnableLinqExpressions="false" AllowAutomaticDeletes="true" runat="server" AllowPaging="true" AllowSorting="true" AllowFilteringByColumn="true" PageSize="10" AutoGenerateColumns="false">
                        <GroupingSettings CaseSensitive="false" />
                        <HeaderStyle Font-Bold="true" />
                        <PagerStyle Mode="NextPrevAndNumeric" AlwaysVisible="true" Visible="true" EnableSEOPaging="true"/>
                        <ClientSettings>
                            <Scrolling AllowScroll="False" />
                        </ClientSettings>
                        <MasterTableView CommandItemDisplay="Top" AllowNaturalSort="false" DataSourceID="srcUsers" DataKeyNames="distinguishedName">
                            <CommandItemTemplate>
                            <div style="padding-left:8px; padding-right:8px; padding-top:6px; padding-bottom:6px;">
                            <table width="100%">
                                <tr>
                                <td>
                                <asp:LinkButton ID="btnAdd" runat="server"><asp:Image ID="imgAdd" runat="server" ImageUrl="~/images/add_button.gif"/> New User</asp:LinkButton>
                                </td>
                                <td align="right" style="vertical-align:middle;">
                                <asp:LinkButton ID="btnColumns" runat="server"><asp:Image ID="imgColumns" runat="server" ImageUrl="~/images/columns16x16.png"/> Choose Columns</asp:LinkButton> |
                                <asp:LinkButton ID="btnRefresh" runat="server" CommandName="RebindGrid"><asp:Image ID="imgRefresh" runat="server" ImageUrl="~/images/refresh_button.gif"/> Refresh</asp:LinkButton> |
                                <asp:LinkButton ID="btnExportCSV" runat="server" onclick="btnExportCSV_OnClick"><asp:Image ID="Image1" runat="server" ImageUrl="~/images/export_to_csv.gif"/> Export CSV</asp:LinkButton>
                                </td>
                                </tr>
                            </table>
                            </div>
                            </CommandItemTemplate>
                            <Columns>
                                <telerik:GridTemplateColumn UniqueName="EditColumn" HeaderText="Edit" AllowFiltering="false" ItemStyle-Wrap="false">
                                    <ItemTemplate>
                                        <asp:HyperLink runat="server" ID="lnkEdit"><img src="images/edit_button.gif" alt="Edit" /></asp:HyperLink>
                                        <asp:Label runat="server" ID="lblobjectGUID" Visible="false" Text="<%# bind('objectGUID') %>"/>
                                        <asp:Label runat="server" ID="lblDistinguishedName" Visible="false" Text="<%# bind('distinguishedName') %>"/>
                                    </ItemTemplate>
                                </telerik:GridTemplateColumn>
                                <telerik:GridBoundColumn HeaderText="First Name" UniqueName="cbFirstName" DataField="givenName" FilterListOptions="VaryByDataType" FilterImageUrl="~/images/filter_icon.gif" />
                                <telerik:GridBoundColumn HeaderText="Last Name" UniqueName="cbLastName" DataField="sn" FilterListOptions="VaryByDataType" FilterImageUrl="~/images/filter_icon.gif" />
                                <telerik:GridBoundColumn HeaderText="Display Name" UniqueName="cbDisplayName" DataField="displayName" FilterListOptions="VaryByDataType" FilterImageUrl="~/images/filter_icon.gif" display="false"/>
                                <telerik:GridBoundColumn HeaderText="userPrincipal Name" UniqueName="cbUserPrincipal" DataField="userPrincipalName" FilterListOptions="VaryByDataType" FilterImageUrl="~/images/filter_icon.gif" display="false"/>
                                <telerik:GridBoundColumn HeaderText="State" UniqueName="cbState" DataField="st" FilterListOptions="VaryByDataType" FilterImageUrl="~/images/filter_icon.gif" display="false"/>
                                <telerik:GridBoundColumn HeaderText="Country" UniqueName="cbCountry" DataField="co" FilterListOptions="VaryByDataType" FilterImageUrl="~/images/filter_icon.gif" display="false"/>
                                <telerik:GridBoundColumn HeaderText="City" UniqueName="cbCity" DataField="l" FilterListOptions="VaryByDataType" FilterImageUrl="~/images/filter_icon.gif" display="false"/>
                                <telerik:GridBoundColumn HeaderText="Department" UniqueName="cbDepartment" DataField="department" FilterListOptions="VaryByDataType" FilterImageUrl="~/images/filter_icon.gif" display="false"/>
                                <telerik:GridBoundColumn HeaderText="Fax" UniqueName="cbFax" DataField="facsimileTelephoneNumber" FilterListOptions="VaryByDataType" FilterImageUrl="~/images/filter_icon.gif" display="false"/>
                                <telerik:GridBoundColumn HeaderText="Mobile" UniqueName="cbMobile" DataField="mobile" FilterListOptions="VaryByDataType" FilterImageUrl="~/images/filter_icon.gif" display="false"/>
                                <telerik:GridBoundColumn HeaderText="Telephone Number" UniqueName="cbNumber" DataField="telephoneNumber" FilterListOptions="VaryByDataType" FilterImageUrl="~/images/filter_icon.gif" display="false"/>
                                <telerik:GridBoundColumn HeaderText="Company" UniqueName="cbCompany" DataField="company" FilterListOptions="VaryByDataType" FilterImageUrl="~/images/filter_icon.gif" display="false"/>
                                <telerik:GridBoundColumn HeaderText="Postal Code" UniqueName="cbPostal" DataField="postalCode" FilterListOptions="VaryByDataType" FilterImageUrl="~/images/filter_icon.gif" display="false"/>
                                <telerik:GridBoundColumn HeaderText="Street Address" UniqueName="cbAddress" DataField="streetAddress" FilterListOptions="VaryByDataType" FilterImageUrl="~/images/filter_icon.gif" display="false"/>
                                <telerik:GridBoundColumn HeaderText="Email Address" SortExpression="mail" ItemStyle-Wrap="false" UniqueName="cbEmailAddress" DataField="mail" DataType="System.String" ItemStyle-Width="100%" HeaderStyle-Width="100%" FilterListOptions="VaryByDataType" FilterControlWidth="150" FilterImageUrl="~/images/filter_icon.gif" display="false" />
                                <telerik:GridBoundColumn HeaderText="Forwarder" ItemStyle-Wrap="false" UniqueName="cbForwarder" DataField="altRecipient" FilterListOptions="VaryByDataType" FilterImageUrl="~/images/filter_icon.gif" display="false" />
                                <telerik:GridBoundColumn HeaderText="Full Access Permission" ItemStyle-Wrap="false" UniqueName="cbFullAccess" DataField="extensionAttribute8" FilterListOptions="VaryByDataType" FilterImageUrl="~/images/filter_icon.gif" display="false" />
                                <telerik:GridBoundColumn HeaderText="Mailbox Size (MB)" ItemStyle-Wrap="false" UniqueName="cbMailboxSize" DataField="MailboxSize" FilterListOptions="VaryByDataType" FilterImageUrl="~/images/filter_icon.gif" display="false" />
                                <telerik:GridBoundColumn HeaderText="Mailbox Plan" ItemStyle-Wrap="false" UniqueName="cbMailboxPlan" DataField="extensionAttribute10" FilterListOptions="VaryByDataType" FilterImageUrl="~/images/filter_icon.gif" display="false" />
                                <telerik:GridBoundColumn HeaderText="Lync Plan" ItemStyle-Wrap="false" UniqueName="cbLyncPlan" DataField="extensionAttribute3" FilterListOptions="VaryByDataType" FilterImageUrl="~/images/filter_icon.gif" display="false" />
                                <telerik:GridBoundColumn HeaderText="Phone Number" ItemStyle-Wrap="false" UniqueName="cbLyncPhone" DataField="msRTCSIP-Line" FilterListOptions="VaryByDataType" FilterImageUrl="~/images/filter_icon.gif" display="false" />
                                <telerik:GridDateTimeColumn HeaderText="Creation Date" DataFormatString="{0:MM/dd/yyyy hh:mm:ss tt}" ItemStyle-Wrap="false" UniqueName="cbCreatedDate" DataField="whenCreated" FilterListOptions="VaryByDataType" FilterImageUrl="~/images/filter_icon.gif" FilterControlWidth="100px" />
                                <telerik:GridBoundColumn HeaderText="distinguishedName" UniqueName="distinguishedName" DataField="distinguishedName" display="false" FilterListOptions="VaryByDataType" FilterImageUrl="~/images/filter_icon.gif" />
                                <telerik:GridBoundColumn HeaderText="objectGUID" UniqueName="objectGUID" DataField="objectGUID" Display="false" FilterListOptions="VaryByDataType" FilterImageUrl="~/images/filter_icon.gif" />
                                <telerik:GridTemplateColumn HeaderText="Delete" UniqueName="Delete" AllowFiltering="false">
                                    <ItemTemplate>
                                        <asp:ImageButton ID="btnDelete" runat="server" CommandName="Delete" ImageUrl="~/images/delete_button.gif"/>
                                    </ItemTemplate>
                                </telerik:GridTemplateColumn>
                            </Columns>
                        </MasterTableView>
                    </telerik:RadGrid>

[Antonio Stoilkov]

Hello Trevor,

The property have been introduced in the 2012 Q1 official release. Note that you could achieve your scenario by manually setting the RadDatePicker.DateInput.DisplayDateFormat and RadDatePicker.DateInput.DateFormat in the RadGrid ItemCreated event by checking if the current item is GridFilteringItem, finding the RadDatePicker control and assigning the values.

Regards,
Antonio Stoilkov
the Telerik team

[Trevor]

Thanks Antonio,

I am using the default datetimecolumn so what would the name of the control be. I am obviously not customly creating the field. I have the code below but can not find the control.

Protected Sub gridUsers_ItemCreated(sender As Object, e As Telerik.Web.UI.GridItemEventArgs) Handles gridUsers.ItemCreated
        If TypeOf (e.Item) Is GridFilteringItem Then
            For Each Control In e.Item.Controls
                If TypeOf Control Is RadDatePicker Then
                    Dim dpPicker As RadDatePicker = Control
                    dpPicker.DateInput.DisplayDateFormat = "{0:MM/dd/yyyy hh:mm:ss tt}"
                End If
            Next


        End If
    End Sub

Thanks,
Trevor

[Princy]

Hi,

Please try the following code snippet to access the RadDatePicker GridFilteringItem

C#:

protected void gridUsers_ItemCreated(object sender, Telerik.Web.UI.GridItemEventArgs e)
{
    if ((e.Item) is GridFilteringItem)
    {
        GridFilteringItem item=(GridFilteringItem)e.Item;
        RadDatePicker rdpicker = (RadDatePicker)item["cbCreatedDate"].Controls[0];
        rdpicker.DateInput.DisplayDateFormat = "MM/dd/yyyy hh:mm:ss tt";
    }
}

Thanks,
Princy.

[venkat]

I have a GridDateTimeColumn on my radgrid  when i am filter From and to date and apply filter "between" sometime not working  filtering Example From date: 11/1/2018 and To date:11/30/2018 its showing correctly and again i changed From date range as  11/4/2018 now its show no record message 

can please suggestion what is the problem?