Filter is not working with date function

[komdil]

I am trying to use date function for filtering entities by todays date, but date function is not working

Assemblies affected

Microsoft.AspNetCore.OData 7.4.1

Reproduce steps

I have this entity:

new Student
            {
                Id = Guid.NewGuid(),
                Name = name,
                Score = score,
                Date = DateTime.Now,
            }

I am trying to get records which Date is equal to todays date. I executed some queries:
https://localhost:44383/odata/Student?$filter=Date +eq + now()
https://localhost:44383/odata/Student?$filter=Date +eq + date(now())
https://localhost:44383/odata/Student?$filter=date(Date) +eq + date(now())

Expected result

{
    "@odata.context": "https://localhost:44383/odata/$metadata#Student",
    "value": [
        {
            "Id": "b557bf76-8efd-4fb9-8fae-454d86876ed4",
            "Name": "Cody Allen",
            "Score": 130,
            "Date": "2021-03-19T00:00:00+05:00"
        }
    ]
}

Actual result

{
    "@odata.context": "https://localhost:44383/odata/$metadata#Student",
    "value": []
}

[Sreejithpin]

Hi can you please confirm if the filter is not working (as in not returning the expected result) or is it throwing any error?

[SimonPStevens]

I can add that I'm also seeing this behaviour using Microsoft.AspNet.OData 7.4.1 and 7.5.6. No error, but the date function does not appear to be stripping of the time component.

We're using an underlying entity framework context connected to a SQL server instance to resolve the data, and if I profile the query it looks like the time isn't being stripped off. The query is executing against the full date & time even when using date(now()) in the OData filter.

I'm using this filter:

?$filter=Created ge date(now())

But that is causing the underlying query to be executed as this:

However if I filter with an explicit date like this:

?$filter=Created ge 2021-03-23

Note how in this case the WHERE clause is correctly only using the year, month and date parts.

Looking at the queryOptions that come through to the server, I can see the filter with the right components:

[komdil]

@Sreejithpin any workarround?

[SimonPStevens]

@komdil We've worked around it by combining the year, month and date into a single value and comparing that with the year, month and date of the current time. You have to multiply up the year and month so they don't overlap with the day and the combined value is always sequential in the same way a date would be. Like this:

$filter=year(Created) mul 10000 add month(Created) mul 100 add day(Created) ge year(now()) mul 10000 add month(now()) mul 100 add day(now())

[komdil]

@SimonPStevens It works. Can you mark this issue as bug?

[KenitoInc]

Issue resolved by #2457