OData - How to query DateTime greater or equal to given value

Question

I am trying to query my OData service for a data with a datetime greater or equal to given value. It is not working as expected

I have read the documentation:

https://learn.microsoft.com/en-us/odata/webapi/datetime-support#filter-datetime

Assemblies:

• Microsoft.AspNetCore.OData 7.4.0
• NET Core 3
• EF Core

Reproduce steps

I have tried the following query formats:

1. https://myurl/api/resource?$filter=city eq 'Kigali' and purchaseDateTime gt 2020-01-01T23:59:59.99Z &$orderby=purchaseDateTime desc HTTP 500 - Conversion failed when converting date and/or time from character string.

2. https://myurl/api/resource?$filter=city eq 'Kigali' and purchaseDateTime gt cast(2020-01-01T23:59:59.99Z,Edm.DateTimeOffset) &$orderby=purchaseDateTime desc returns HTTP 500 - Internal Server Error

3. https://myurl/api/resource?$filter=city eq 'Kigali' and purchaseDateTime gt datetime'2020-01-01T23:59:59.99Z' &$orderby=purchaseDateTime desc

On the OData site, this works

https://services.odata.org/V4/OData/OData.svc/Products?$filter=ReleaseDate%20gt%202002-12-30T23:59:59.99Z

Expected result

Return the data according to the query in the same way that the the http://services.odata.org responds

Actual results

HTTP Bad Request or HTTP 500

Microsoft says this should work:

GET ~/Customers?$filter=Birthday lt cast(2015-04-01T04:11:31%2B08:00,Edm.DateTimeOffset)
GET ~/Customers?$filter=year(Birthday) eq 2010

Other sources of info I have tried:

1. Filtering dates between x & y using ODATA
2. ODATA DATE QUERY
3. OData query filter for dateTime range

Answer 1

I found the solution.

It turns out that it is related to EF Core and the SQL data type of the column purchaseDateTime that I am querying

The data is stored as SQL Data type: datetime

Without being explicit that it should map to SQL Data type datetime, EF Core sends it as a SQL Data type datetime2

This is the source of the error: Conversion failed when converting date and/or time from character string

The solution

The model should explicitly specify a Column Type of datetime, so that it looks like the following

public class Patient
{
    public ulong PatientId { get; set; }
    public string Name { get; set; }

    public string City { get; set; }

    [Column(TypeName="datetime")] //overrides default of "datetime2"
    public DateTime PurchaseDateTime { get; set; }

    public ICollection<PatientForms> PatientForms { get; set; }
}