This is a migrated thread and some comments may be shown as answers.

Date Filter Improvements

3 Answers 280 Views

Grid

This is a migrated thread and some comments may be shown as answers.

Ed asked on 08 Apr 2009, 03:55 PM

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">

</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

3 Answers, 1 is accepted

answered on 13 Apr 2009, 11:00 AM

125130-datetimefilter1.zip

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.