GridDateTimeColumn filtering localization issues

7 posts, 0 answers
  1. Massimiliano
    Massimiliano avatar
    185 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
    1593 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. Massimiliano
    Massimiliano avatar
    185 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
  4. 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?
  5. Vasil
    Admin
    Vasil avatar
    1593 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
  6. Per
    Per avatar
    120 posts
    Member since:
    Jan 2008

    Posted 14 Dec 2016 in reply to Vasil Link to this post

    Hi

    This is still a problem.
    Using Telerik Grid with Swedish Culture and EnableLinqExpressions="false" will generate a filter looking like this:

    " AND ((startDate >= '2016-12-07,00:00:00') AND startDate <= '2016-12-22,23:59:59'))"

    Since EnableLinqExpressions=false, this should be valid SQL, but it's not.

    The format '2016-12-07,00:00:00' cannot be converted to a Date when running against a MS SQL with Swedish collation.

    You will get a "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value." error.

    I must agree with Massimiliano, you should generat date in ISO format. That way this will always work.

  7. Vasil
    Admin
    Vasil avatar
    1593 posts

    Posted 19 Dec 2016 Link to this post

    Hello Per,

    We reconsidered changing this, and you may track the progress of the issue here:
    https://feedback.telerik.com/Project/108/Feedback/Details/208317-fix-string-representation-of-dates-in-griddatetimecolumn-is-wrong-in-the-filtere


    Regards,
    Vasil
    Telerik by Progress
    Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
Back to Top