Wrong query generated for date filtering?

[Tomislav Tustonic]

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]

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, 

Sample : http://www.syncfusion.com/downloads/support/directtrac/general/ze/gridmvclocalization-1116729563 

 

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]

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]

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]

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

Thanks,
Tom

[Thavasianand Sankaranarayanan]

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.