ODATA DATE QUERY

Question

I need to return records that have a date of 4/4/2013 (for example). The date field in the ODATA service returns as {DATE: "2013-04-04T17:39:33.663"}

How is the query supposed to look like?

So far, I have seen three options to do this, but none of them are straight forward.

Option one: datetime'2013-04-04T12:00': this requires specification of time

Option two: month(DATE) eq 04 day(DATE) eq 04 year(DATE) eq 2013: this is cumbersome

Option three: using option one with 'ge' and 'le' to get records between 2 dates: this is also crap.

This should be very simple as it is in T-SQL.

Answer 1

OData v3 doesn't have a primitive data type that's just Date. The property you have is either a DateTime or a DateTimeOffset, so, whether you're using it or not, there is a time portion of that the value, and if you want to check the value for equality, the time component must be checked as well.

If you know for sure that you never use the time portion (and always set it to 00:00), you could do the following query:

/service.svc/EntitySet?$filter=DateProperty+eq+datetime'2013-04-04'

which implies a time portion of 00:00. But that's just shorthand. And you could have unexpected results if some of your DateTimes do wind up with time portions that are not 00:00.

So, given that you just want to check the date portion of the value, and not full equality, I think the second approach you mention is the best way of going about it:

/service.svc/EntitySet?$filter=day(DateProperty)+eq+4+and+month(DateProperty)+eq+4+and+year(DateProperty)+eq+2013

That way you're checking exactly what you mean to be checking and nothing more.

For what it's worth, I believe a Date datatype is coming in OData v4. Then you'll be able to use equality checking without worrying about the time.

Answer 2

In addition I want to say that you can also use LINQPAD for generation OData requests. You should make LINQ request and LINQPAD will generate correct URI. For example:

• Linq:

from ev in Events where ev.Start >= DateTime.Now.Date select ev

• LINQPAD generates Odata URI:

  http://yoursite/_vti_bin/listdata.svc/Events()?$filter=Start ge datetime'2013-12-05T00:00:00+01:00'

• Adjust it to correct format. In my case I delete "+01:00".

Also you can use Visual studio to get Odata request.