GridDateTimeColumn filtering localization issues

5 posts, 0 answers
  1. Massimiliano
    Massimiliano avatar
    184 posts
    Member since:
    Oct 2012

    Posted 11 Jul 2013 Link to this post

    I seem to have some issue with GridDateTimeColumn with a culture different from the default.
    In particular I use RadGrid1.Culture = New CultureInfo("it-IT") wich uses a date time format like this dd/MM/yyyy HH:mm
    I set in the declarative aspx section (web forms) of GridDateTimeColumn those settings: 
    DataFormatString="{0:dd/MM/yyyy}"
    PickerType="DatePicker"
    FilterDateFormat="dd/MM/yyyy"
    EnableTimeIndependentFiltering="true"

    Nonetheless I'm facing 2 problems when filtering:
    1- It seems dd/MM format is not recognized and filtering always happens with MM/dd format
    2- The resulting SQL (not LINQ) FilterExpression wich converts date to string outputs something like
    ([CreateDate] >= '22/06/2013,00:00:00') AND ([CreateDate] <= '22/06/2013,23:59:59')
    wich results on its own in 2 problems: 
    1- SQL server doesn't seem to understand  '22/06/2013,00:00:00' but likes more '22/06/2013 00:00:00' (without the comma)
    2- It would have been better to have an ISO format like  '2013/06/22 00:00:00' as SQL filter expression output for the same localization issues you may have with an SQL Server (or other db server) installation that has MM/dd default instead of dd/MM, resulting in an out of range exception for dates like this '22/06/2013,00:00:00'

    I'm handling server side filtering in NeedDataSource event, so I could parse the MasterTableView.FilterExpression but this would involve a lot of work, in particular when multiple filters are involved... is there a simpler way of doing it altering the filter expression when it's created and before it can be retrieved from the NeedDataSource event, so to easily transform 
    ([CreateDate] >= '22/06/2013,00:00:00') AND ([CreateDate] <= '22/06/2013,23:59:59')
    to
    ([CreateDate] >= '2013/06/22 00.00.00') AND ([CreateDate] <= '2013/06/22 23.59.59')

    Also (as a suggestion) I would consider outputting this last ISO format by default as an SQL MasterTableView.FilterExpression result in the future Grid versions. This alone would solve all localization issues because you can handle the conversion from input to ISO string based on FilterDateFormat value.




  2. Vasil
    Admin
    Vasil avatar
    1547 posts

    Posted 16 Jul 2013 Link to this post

    Hi Massimiliano,

    Indeed there is bug inside the GridDateTimeColumn. It uses the InvariantCulture for building the filter expression, but the space is wrongly replaced with comma during the building of the expression. It is working with MSSQL as it can actually be tested in our demo here:
    http://demos.telerik.com/aspnet-ajax/grid/examples/generalfeatures/filtering/defaultcs.aspx
    Since it was working correct, our tests ware passing, but I now see that it can cause problems with other data providers.

    We will fix this bug, and as an appreciation of your feedback we are updating your telerik points.

    Regards,
    Vasil
    Telerik
    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 the blog feed now.
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Massimiliano
    Massimiliano avatar
    184 posts
    Member since:
    Oct 2012

    Posted 16 Jul 2013 Link to this post

    Thank you Vasil.
    If you are going to fix the comma issue, consider the ISO format as well, since if MSSQL Server (for example) uses another culture (like dd/MM/yyyy) this will be going to fail as well.
    If instead you pass the "yyyyMMdd HH:mm:ss" format as a string, no matter the culture it will fit.

    http://www.mcs.vuw.ac.nz/technical/software/SGML/doc/iso8601/ISO8601.html

    Best wishes
  5. Dan Pettersson
    Dan Pettersson avatar
    20 posts
    Member since:
    Sep 2009

    Posted 22 Oct 2015 in reply to Vasil Link to this post

    We're using Telerik from Q1 2014 but still get this comma in the dates in the FilterExpression. Whats wrong?
  6. Vasil
    Admin
    Vasil avatar
    1547 posts

    Posted 22 Oct 2015 Link to this post

    Hello Dan,

    We performed further testing on this issue and in fact the comma is placed correctly. For example when using between filter, this expression is correct:
    ([CreateDate] >= '22/06/2013,00:00:00') AND ([CreateDate] <= '22/06/2013,23:59:59')

    The problem with filtering happens when the data column does not have correct type. In this case the column is filtered as String column and does not return matches. 

    If you add columns into DataTable server side, make sure you set correct type.
    For example:

    DataTable data = new DataTable();
    data.Columns.Add("CreateDate");

    Should be:
    DataTable data = new DataTable();
    data.Columns.Add("CreateDate", typeof(DateTime)); 

    Regards,
    Vasil
    Telerik
    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 Feedback Portal and vote to affect the priority of the items
Back to Top
UI for ASP.NET Ajax is Ready for VS 2017