Telerik Forums / UI for ASP.NET AJAX Forum / Grid /

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

Telerik UI for ASP.NET AJAX
Resources Buy Try

10 posts, 0 answers

Trevor

22 posts

Member since:
Jul 2010

Posted 06 May 2012 Link to this post

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

2733 posts

Member since:
May 2010

Posted 07 May 2012 Link to this post

Hello,

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

Thanks,
Jayesh Goyani
Princy

17421 posts

Member since:
Mar 2007

Posted 07 May 2012 Link to this post

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

22 posts

Member since:
Jul 2010

Posted 07 May 2012 Link to this post

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
Admin

530 posts

Posted 09 May 2012 Link to this post

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

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.
Trevor

22 posts

Member since:
Jul 2010
Posted 19 Dec 2012 Link to this post

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>

Filterdate.PNG
Antonio Stoilkov
Admin

530 posts

Posted 21 Dec 2012 Link to this post

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

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.
Trevor

22 posts

Member since:
Jul 2010

Posted 21 Dec 2012 Link to this post

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

17421 posts

Member since:
Mar 2007

Posted 26 Dec 2012 Link to this post

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

1 posts

Member since:
Nov 2018
Posted 22 Nov 2018 in reply to Princy Link to this post

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?

Capture.png

record.png

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

Trevor

Jayesh Goyani

Princy

Trevor

Antonio Stoilkov

Trevor

Antonio Stoilkov

Trevor

Princy

venkat