GridDateTimeColumn filtering localization issues

13 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
    1625 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
    1625 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
    1625 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.
  8. Blanca
    Blanca avatar
    2 posts
    Member since:
    Dec 2017

    Posted 24 Jan 2018 in reply to Vasil Link to this post

    Hello,

    That link gives me error. I am still having the same problem. Using culture "ES" I am getting the filter expression like ([CreateDate] >= '22/06/2013,00:00:00') AND ([CreateDate] <= '22/06/2013,23:59:59') and those commas SQL Server does not understand them.

    Can you give me a solution for this???

    Thank you

     

  9. Eyup
    Admin
    Eyup avatar
    3647 posts

    Posted 29 Jan 2018 Link to this post

    Hi Blanca,

    This is the built-in behavior of RadGrid and this is not going to change, because it may introduce some serious breaking changes related to the filtering functionality of the grid. The removed feedback item link will be visible once again and its status will be updated accordingly.

    Let me clarify and point out some crucial key points:

    1. RadGrid binding:
    First of all, please make sure that you are not using the DataBind() method to bind the grid. Performing complex grid operations such as Inserting, Deleting, Updating, Hierarchy relations, Grouping, Exporting, Paging, Sorting, Filtering, etc. require accommodating appropriate database operations.  Therefore, we suggest you to avoid Simple Databinding and strongly recommend the use of more advanced databinding methods, which automatically handle the aforementioned functions:

    Declarative DataSource (DataSourceID property)
    Programmatic Data Binding (NeedDataSource event, + DetailTableDataBind for hierarchy). You should set the DataSource property ONLY within these event handlers.

    Here is another sample with the NeedDataSource event.


    2. You should let the grid to handle its filtering itself.
    The filtering feature should works properly regardless of the culture.

    3. In some custom scenarios, it may be necessary to get the filtering expression.

    The grid will always build internally its FilterExpression depending on the server Culture (Culture property of the Page). For example, if the Current Thread's culture is en-US, the month will be first, and if its fr-FR, the day will be first:
    https://en.wikipedia.org/wiki/Date_format_by_country

    The FilterExpression string built for the grid is different depending on this property:
    <telerik:RadGrid ... EnableLinqExpressions="true">

    If it is set to true, you can also check the result returned by this method:
    RadGrid1.MasterTableView.GetEntitySqlFilterExpression()

    Alternatively, can use the ItemCommand event handler of the grid and extract the required info manually:
    http://docs.telerik.com/devtools/aspnet-ajax/controls/grid/how-to/Filtering/operate-with-the-filterexpression-manually

    I hope the clarification was helpful.


    Regards,
    Eyup
    Progress Telerik
    Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  10. Blanca
    Blanca avatar
    2 posts
    Member since:
    Dec 2017

    Posted 29 Jan 2018 in reply to Eyup Link to this post

    Hello,

    Thank you for the response.

    I am already using NeedDataSource event to bind the data to the grid. The point is that I want to filter the data on database side, so in the needDatasource function, I extract the filterExpression and "pass it" to the SQL query, so I get from the database the data already filtered. I have been doing this with all filters and it was working fine, even when I use only one date filter. But when it comes to filter BETWEEN two dates,  ([CreateDate] >= '22/06/2013,00:00:00') AND ([CreateDate] <= '22/06/2013,23:59:59') that Comma between date and time is giving me trouble. (The dateformat depending on culture is working fine).

    If you say this behaviour is not going to change, it's ok, I will try to do it in some other way.

    Thanks anyway for the response.

     

  11. Grant
    Grant avatar
    12 posts
    Member since:
    Aug 2011

    Posted 05 Nov 2018 Link to this post

    Hi,

    We are using the FilterExpression from the Grid (EnableLinqExpressions=false) in order to get exactly the same dataset in a SelectCommand in the code behind as that which user has selected using the grid filters on the page. This works well 90% of the time but I have just realised this problem with griddatetimecolumns. If the user adds a filter in a datetime column then the filter expression is no longer valid SQL and the SelectCommand in the code behind returns the error:

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

    The comment from Vasil previously "It is working with MSSQL as it can actually be tested in our demo here:" is not true, it is not valid SQL and doesn't work.

    The filter expression returned from the grid is:

    ([UndocumentedChangesWarningFlag] <> True) AND ([DocumentDetailNo] IS NULL) AND (([CreatedDate] >= '05-Nov-2018,12:00:00,AM') AND ([CreatedDate] <= '05-Nov-2018,11:59:59,PM'))

    The commas are messing us up and seem to be the only stumbling block with using the filter expression as a WHERE clause for a MSSQL select command.

    Do you have any advice on how to deal with this? The times don't actually matter so if it is easy to remove the time part of the dates in the filterexpression then that would work for us.

  12. Grant
    Grant avatar
    12 posts
    Member since:
    Aug 2011

    Posted 05 Nov 2018 Link to this post

    Also, we cannot use EnableLinqExpressoins=true
  13. Attila Antal
    Admin
    Attila Antal avatar
    204 posts

    Posted 08 Nov 2018 Link to this post

    Hi Grant and Blanca,

    We have created a workaround for the issue that may be a viable solution for the given scenario.The solution is posted in the following Knowledge Base article: Get SQL compliant FilterExpressions from RadGrid

    Kind regards,
    Attila Antal
    Progress Telerik
    Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
Back to Top