OData: Date "Greater Than" filter

Solution 1:

Figured this out.

OData V2 out-of-the-box returns dates out of SQL in JSON Date format like so:

/Date(1338282808000)/

However, in order to use a date as a filter within an OData call, your date has to be in EDM format, looking like this:

2012-05-29T09:13:28

So, I needed to get the date from my initial OData call, then convert it to the EDM format for use in my subsequent OData calls, which look like this:

/Services/v001.svc/Orders?$filter=close_dt gt DateTime'2012-05-29T09:13:28'

I ended up creating a javascript function that does the formatting switcharoo:

function convertJSONDate(jsonDate, returnFormat) {
        var myDate = new Date(jsonDate.match(/\d+/)[0] * 1);
        myDate.add(4).hours();  //using {date.format.js} to add time to compensate for timezone offset
        return myDate.format(returnFormat); //using {date.format.js} plugin to format :: EDM FORMAT='yyyy-MM-ddTHH:mm:ss'
    }

A couple of notes:

The JSON format does not seem to adjust for timezone, so the date returned does not match the date I see in my database. So I had to add time manually to compensate (someone please explain this).
I am using the date.format.js plugin which you can download here for formatting the date and adding time.

Solution 2:

In OData V4 date filtering format has changed to $filter=close_dt gt 2006-12-30T23:59:59.99Z

For example

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

For previous versions of OData see previous answers

Solution 3:

If you use the datetime logic, you can do lt or gt.

e.g. ...mydomain/Services/v001.svc/Orders?$filter=close_dt gt datetime'20141231'

OData: Date "Greater Than" filter

Solution 1:

Solution 2:

Solution 3:

Related

Recent Posts