Filtering dates between x & y using ODATA

Question

I'm looking into the new ASP.NET Web API as a reporting tool.

In SQL I would do this:

WHERE order_date 
    BETWEEN to_date ('2003/01/01', 'yyyy/mm/dd') 
    AND to_date ('2003/12/31', 'yyyy/mm/dd');

...how do these type of command translate in the ODATA protocol URL?

Solution 1

With the Visual Studio 2012.2 update, OData support is back and the following url works for filtering dates:

http://host/api/controller?$filter=order_date+gt+datetime'2003-01-01'+and+order_date+lt+datetime'2003-12-31'

If you want to include a time, you should specify the dates in ISO 8601 format.

Solution 2

Using the WCF Data Services client API, you would be able to express the date constraints using a LINQ clause, e.g. context.Orders.Where(o => o.OrderDate < DateTime.Now && o.OrderDate > DateTime.Now.AddDays(-7)). In the URL that is generated from that LINQ query, you'll see query parameters such as the $filter option, e.g. http://services.odata.org/(S(readwrite))/OData/OData.svc/Products()?$filter=(ReleaseDate lt datetime'2012-06-19T08:16:01.4283521-07:00') and (ReleaseDate gt datetime'2012-06-26T08:16:01.4293524-07:00'). LINQPad is a great way to see what those URLs would look like if you're familiar with LINQ

Comments

[Mark Stafford - MSFT]

(Note that I don't think that type of URL will work on today's Web API OData implementation as I believe they are still working on implementing support for $filter.)

[Mark Stafford - MSFT]

It's only disappearing while they make some modifications (e.g., they are taking a dependency on ODataLib/Microsoft.Data.OData) and ensure that the quality is where it should be for a production release. There is a definite future for Web API + OData.

[tugberk]

@MarkStafford-MSFT thanks for the clarification. Brad also mentioned that it will be an out of band support: twitter.com/bradwilson/status/215660980678569984

[Pruthviraj]

Hi I am trying to filter Customers whose dob is > 1990 in Odata Using C#. How can I do that? Following is what I have return now, Customer = await ODataClient.For<Customer>().Filter(y => y.CustomerNo == CustomerNo && y => y.CustomerDOB > CustomerDOB ).FindEntriesAsync();

[mhu]

@Pruthviraj: You should open a new question

[Pruthviraj]

@mhu thanks a lot replying, Initially I misunderstood the problem. '>', '>=' and other comparison operators actually works with date object.