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

GridDateTimeColumn filtering localization issues

12 Answers 178 Views

Grid

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

Massimiliano asked on 11 Jul 2013, 11:46 AM

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.

12 Answers, 1 is accepted

answered on 16 Jul 2013, 10:11 AM

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.

answered on 22 Oct 2015, 02:25 PM

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

answered on 14 Dec 2016, 02:14 PM

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.

answered on 29 Jan 2018, 09:24 AM

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.

answered on 29 Jan 2018, 10:05 AM

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.

answered on 05 Nov 2018, 11:56 PM

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.