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

1
votes

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://docs.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
2
c#datetimeodataasp.net-core-webapi
Do not use cast. Use string now = DateTime.Now.ToString("yyyy-MM-ddThh:mm:ssZ"); – jdweng
It's not .NET, thats an OData query @jdweng – user919426
Could u provide your model? – Michael Wang
Could u get data when access https://myurl/api/resource? I tested your queries and it works for me. – Michael Wang
You should post the $metadata section that defines the resource you are querying, also the results of a `?$filter=city eq 'Kigali'&$top=2' That should providse – Chris Schaller

2 Answers

4
votes

For your first two queries, it works for me.

 https://localhost:44339/odata/Patients?$filter=city eq 'Kigali' 
                                and purchaseDateTime gt 2020-01-01T23:59:59.99Z 
                                &$orderby=purchaseDateTime desc

enter image description here

 https://localhost:44339/odata/Patients?$filter=City eq 'Kigali' 
                                and PurchaseDateTime gt cast(2020-01-01T23:59:59.99Z,Edm.DateTimeOffset)
                                &$orderby=PurchaseDateTime desc

enter image description here

Here is codes of model.

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

    public string City { get; set; }

    public DateTime PurchaseDateTime { get; set; }

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

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; }
}