GridDateTimeColumn filtering localization issues

[Massimiliano]

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.

[Vasil]

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

[Massimiliano]

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

[Dan Pettersson]

We're using Telerik from Q1 2014 but still get this comma in the dates in the FilterExpression. Whats wrong?

[Vasil]

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

[Per]

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.

[Vasil]

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

[Blanca]

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

 

[Eyup]

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

[Blanca]

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.

 

[Ian]

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.

[Ian]

Also, we cannot use EnableLinqExpressoins=true

[Attila Antal]

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