Date Filter Improvements

4 posts, 0 answers
  1. Ed
    Ed avatar
    15 posts
    Member since:
    Apr 2009

    Posted 08 Apr 2009 Link to this post

    I've been battling the best way to handle filters on a grid date field to get them to work properly.  The date/time is coming from an MSSQL database which the date fields have been populated with date and time.  I do not currently have anything special for the field column in the grid:

     

    <telerik:GridDateTimeColumn datafield="ExportedDate" DataFormatString="{0:MM/dd/yyyy}" datatype="System.DateTime"
            
    headertext="Exported"  sortexpression="ExportedDate" uniquename="EXPORTED" FilterControlWidth="75px">

     

     

        <HeaderStyle HorizontalAlign="Center" Width="90px"/>

     

     

        <ItemStyle HorizontalAlign="Right" Width="90px"/>

     

     

    </telerik:GridDateTimeColumn>

    What I have found is that the date will not work correctly on fields unless the data coming from the database has the time essentially set  to 00:00:00.  All the filtering is done by the specified date with the time set to midnight.

    For example: if my date field from the database is 3/25/2009 06:30:12 and I'm filtering on the date 3/25/2009, then the following results ocurr:
            Comparison                        Expected Result        Actual Result
            --------------------------          -----------------------     --------------------
            EqualTo                              Match                        No match
            NotEqualTo                         No Match                   Match
            GreaterThan                       No Match                   Match
            LessThan                            No Match                   No Match
            GreaterThanOrEqualTo      Match                        Match
            LessThanOrEqualTo           Match                        No Match

    As you can see, only 2 comparisons return results as expected.  It all stems around the issue with the time component being used in the comparison.

    Work arounds include adding custom filtering, changing every database access procedure or call to remove the time component in the results, and limiting the filter functions to only what works.

    None are very attractive when considering the extent of work involved.  This system that the Telerik controls are used was initially developed 10 years ag0 and has grown to have at one site alone, 300,000 registered users, hundreds of web pages, thousands of queries, and, needless to say, an extremely large amount of accumulated data.

    I believe I shouldn't have to add code to make the date filter work as logically expected.  The filtering logic should automatically strip off the TimeOfDay and not be used in the comparisons.  Because the System.DateTime has a resolution down to the milliseconds, anyone searching for any equality based on the date will not find a match.  This is especially true when picking a date from the calendar icon or when typing in a date; you do not specifiy the time and it is implied to be midnight.

    Any assistance, comments, or improvements is greatly appreciated.

    Ed Lamprecht

  2. Georgi Krustev
    Admin
    Georgi Krustev avatar
    3706 posts

    Posted 13 Apr 2009 Link to this post

    Hello Ed,

    Thank you for your comparison diagram.

    Actually it describes expected behavior. As you pointed the filtered result depends not only on the date, but the time as well. In this case, the grid can filter only the data bound to it. In other words, if the data field in the database is datetime, it is normal for the grid to compare the time values too.

    To achieve filtering based only on date and if the time is not important for further filtration, you can easily convert the datetime field into a date and return it as a date type only. The framework will convert it to a DateTime type, but in this case the time will be set to midnight.

    A project which shows how to accomplish this task is attached to this thread (see the SELECT query for details).

    Best regards,
    Georgi Krustev
    the Telerik team

    Check out Telerik Trainer , the state of the art learning tool for Telerik products.
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Michael O'Flaherty
    Michael O'Flaherty avatar
    67 posts
    Member since:
    Jun 2010

    Posted 12 Apr 2012 Link to this post

    Thanks! We prefer our solution because it allows us to display the time in the grid. In your example, it looks to me like you are chopping off the time in the display. We wanted to display the time but treat the query as a date only. Our solution allows that.
  5. Michael O'Flaherty
    Michael O'Flaherty avatar
    67 posts
    Member since:
    Jun 2010

    Posted 12 Apr 2012 Link to this post

    Sorry, replied to the wrong thread.
Back to Top