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

Kendo Grid Server Side filtering is not working for DateTime columns

4 Answers 1100 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Gilberto
Top achievements
Rank 1
Gilberto asked on 16 Nov 2018, 11:56 AM
I am using a DateTime column with filterable options enabled. When I apply a filter on this column, the request.Filters has a valid DateTime but it is applying an incompatible format when it calls SQL Server. Here is my code:

    public async Task<JsonResult> OnGetDataAsync([DataSourceRequest]DataSourceRequest request, CustomFilterModel customFilters)
    {
        IQueryable<DAL.Product> products = this.productService.GetByCriteria(customFilters);

        JsonSerializerSettings jsonSettings = new JsonSerializerSettings()
        {
            ContractResolver = new DefaultContractResolver()
        };

        return new JsonResult(await products.ToDataSourceResultAsync(request), jsonSettings);
    }

This is what EF generates: (abbreviated for clarity)

    SELECT ....
    WHERE [p].[DateAdded] <= '2018-10-01T00:00:00.0000000'

**Error:** System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting date and/or time from character string.

Basically, the generated SQL query does not work when I run it directly in SMMS. It only works if I convert the date to **'2018-10-01T00:00:00'**

Tech stack: .NET Core 2.2, Razor Pages, Kendo 2018.3.911, SQL 2018

4 Answers, 1 is accepted

Sort by
0
Viktor Tachev
Telerik team
answered on 21 Nov 2018, 11:05 AM
Hi Gilberto,

The error you are seeing can be caused by a known issue in EntityFramework Core. It should be fixed in the latest EntityFramework release and the date should be passed in the correct format to the database. You can see more about the issue in the following GitHub issues:


With that said, would you make sure that you are using the latest EntityFramework Core version and see how the behavior changes. 

Regards,
Viktor Tachev
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.
Jakob
Top achievements
Rank 1
commented on 15 Oct 2021, 05:19 PM

Three years later, I know.   I don't think the bug is just in entity framework, but there's a Kendo Grid bug too.

 

We retrieve a list of items for a grid that uses a strongly typed DateTime field, but yet even though the calendar control displays, etc. the filter never works even if all the text field filters work fine.

Tsvetomir
Telerik team
commented on 20 Oct 2021, 11:04 AM

Hi Jacob, please note that if you are using the equals operator for the date comparison, then, the filtering will be done against the specific seconds. The other discussions in this thread target a server-side error introduced by the EF. Could you confirm that there is no server-side error that you observe?

If the time portion is the root of the problem, I recommend following the suggestions from the following thread:

https://www.telerik.com/forums/date-filter-doesn-t-ignore-the-time

0
Paito
Top achievements
Rank 2
answered on 08 Feb 2019, 05:02 PM

I'm getting the same error as well filtering DateTimes, and I am using the latest and greatest:

<PackageReference Include="Microsoft.EntityFrameworkCore" Version="2.2.1" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="2.2.1" />
<PackageReference Include="Telerik.UI.for.AspNet.Core" Version="2019.1.115" />

SqlException: Conversion failed when converting date and/or time from character string.

Any ideas?

1
Viktor Tachev
Telerik team
answered on 13 Feb 2019, 11:59 AM
Hi Paito,

There is an open issue about the error you are seeing. You can find more information on it and a possible workaround in the discussing on GitHub:



Regards,
Viktor Tachev
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.
3
Paito
Top achievements
Rank 2
answered on 13 Feb 2019, 02:17 PM

Thanks! I was able to fix by adding this in the DbContext:

.Property(e => e.DateTime).HasColumnType("datetime")

Tags
Grid
Asked by
Gilberto
Top achievements
Rank 1
Answers by
Viktor Tachev
Telerik team
Paito
Top achievements
Rank 2
Share this question
or