We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on: November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Wrong query generated for date filtering?

Thread ID:

Created:

Updated:

Platform:

Replies:

143480 Mar 21,2019 06:20 PM UTC Mar 26,2019 09:19 AM UTC ASP.NET MVC - EJ 2 5
loading
Tags: Grid
Tomislav Tustonic
Asked On March 21, 2019 06:20 PM UTC

Hello

It seems that the filtering by date, when asking for an exact date, generates a wrong SQL query.

When I try to filter my grid for an exact date 2018-06-20, the generated predicates in the DataManagerRequest Where are wrong:

-        predicates    Count = 2    System.Collections.Generic.List<Syncfusion.EJ2.Base.WhereFilter>
-        [0]    {Syncfusion.EJ2.Base.WhereFilter}    Syncfusion.EJ2.Base.WhereFilter
        Condition    null    string
        Field    "EmlMessage.ReceivedTime"    string
        IgnoreCase    false    bool
        IsComplex    false    bool
        Operator    "greaterthan"    string
+        predicates    null    System.Collections.Generic.List<Syncfusion.EJ2.Base.WhereFilter>
        value    "2018-06-19T21:59:59.000Z"    object {string}
-        [1]    {Syncfusion.EJ2.Base.WhereFilter}    Syncfusion.EJ2.Base.WhereFilter
        Condition    null    string
        Field    "EmlMessage.ReceivedTime"    string
        IgnoreCase    false    bool
        IsComplex    false    bool
        Operator    "lessthan"    string
+        predicates    null    System.Collections.Generic.List<Syncfusion.EJ2.Base.WhereFilter>
        value    "2018-06-19T22:00:01.000Z"    object {string}

Which gives Count query as:

SELECT
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT
        COUNT(1) AS [A1]
        FROM [dbo].[ProcessedEmails] AS [Extent1]
        WHERE ([Extent1].[EmlMessage_ReceivedTime] > convert(datetime2, '2018-06-19 23:59:59.0000000', 121)) AND ([Extent1].[EmlMessage_ReceivedTime] < convert(datetime2, '2018-06-20 00:00:01.0000000', 121))
    )  AS [GroupBy1]

Which gives 0 results

The second date should be 2018-06-20 23:59:59.0000000

Tom

Pavithra Subramaniyam [Syncfusion]
Replied On March 22, 2019 09:48 AM UTC

Hi Tomislav, 

Thanks for contacting Syncfusion support. 

We would like to inform you that by default when filtering a date column, the query for “equal” will be passed with predicate including “greater than” and “less than” to fetch the date value entered. This is the default filtering query standard followed for filtering a date column value. We have prepared a sample for your convenience, please download the sample from the link below, 
 
In the above sample when filtering a date column with value as “7/2/1995”, the predicates in “dm.Where” will be like the below screenshot. 

 

If we have misunderstood your query, we need more details to further look into this and provide you a solution. Could you please share with us the following details for better assistance, 
  1. Share the detailed description of the problem you are facing.
  2. Share the exact scenario/proper replication procedure of the problem.
  3. What type of filter are you using.
  4. Are you using any custom codes to perform filter in Grid.
  5. Share the values you are using to the date column.
  6. Share the full grid code.

Or if you are facing any server time zone problem. Then we suggest you to set the “serverTimezoneOffset” based on the following code. Please use the following code in your sample to overcome this time zone problem. Please refer to the following solution, 
 
<script>  
    ej.data.DataUtil.serverTimezoneOffset = (2 * (new Date().getTimezoneOffset() / 60));   //Set the servertimezoneoffset 
</script>  
  
Please get back to us if you need further assistance. 
 
Regards, 
Pavithra S. 


Tomislav Tustonic
Replied On March 22, 2019 05:13 PM UTC

Hello
I tried your sample and it works fine.

However, there's a difference between the date pickers in the sample and in my application. My has a time picker, while yours does not:


I can't see where this difference comes from. Here's a (extremely simplified) definition of my grid:

@(Html.EJS().Grid("grid").EnablePersistence(false)
.ShowColumnChooser(true)
.Locale("hr")
.AllowPaging(true).PageSettings(p => p.PageSize(10))
.AllowFiltering(true)
.FilterSettings(fs => fs.Type(Syncfusion.EJ2.Grids.FilterType.Menu))
.DataSource(Model.MailsDataSource)
.Columns(col =>
{
    col.Field("Id").Visible(false).IsPrimaryKey(true).ShowInColumnChooser(false).Add();
    col.Field("EmlMessage.ReceivedTime").HeaderText("Email received")
        .Type("datetime").Format("short")
        .ShowInColumnChooser(true).Add();
})
.Render()
)

So, I'd like to turn off the time picker, but also I'd like to know why it appears in my application and does not in your demo.

Thanks,
Tom




Thavasianand Sankaranarayanan [Syncfusion]
Replied On March 25, 2019 05:41 AM UTC

Hi Tom, 

We have analyzed your query. To turn off the “time picker” and make it display as “date picker” we suggest you to set the “Type” property of column as “date”. Based on the type of the column, grid will automatically render “date picker” or “date time picker” filtering in Grid. So we suggest you to use the below code to render a “date picker” in the filtering menu. 

 
col.Field("OrderDate").Type("date").Format("yMd").HeaderText("OrderDate").Width("150").Add(); 


Please get back to us if you need further assistance. 

Regards, 
Thavasianand S. 


Tomislav Tustonic
Replied On March 25, 2019 01:33 PM UTC

That's it.
Now the filtering works as expected.

Thanks,
Tom

Thavasianand Sankaranarayanan [Syncfusion]
Replied On March 26, 2019 09:19 AM UTC

Hi Tomislav, 
 
We are happy that the problem has been solved. 
 
Please get back to us if you need any further assistance.  
                          
Regards, 
Thavasianand S.

CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Please sign in to access our forum

This page will automatically be redirected to the sign-in page in 10 seconds.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

;