54

Is there a way to return a series of records in OData by specifying a "Date greater than xxxxx" filter...but using a Date that was previously obtained form an OData feed?

Use Case: Pretend that I want to build a web page that displays a list of the most recently completed online orders. This is what I'm aiming for:

  1. Load the page
  2. Hit my OData service asynchronously, returning the last 100 orders (ordering by date descending so that the most recently completed order shows up first)
  3. Build the HTML on the page using the OData data
  4. Store the MAX date into a global variable (looks like this: /Date(1338336000000)/)
  5. Hit the OData service on a 30 second interval but this time specify a filter to only return records where the order date is greater than the previous MAX Date. In this case: /Date(1338336000000)/
  6. If any records are returned, build the HTML for those records and prepend the items to the previously loaded items.

Where I am struggling is in specifying the Date "greater than" filter. For some reason, the date filters in OData do not seem to play very nice with OData's own native date format. Do I need to convert the date originally obtained into a different format that can be used for filtering?

I want to do something like this:

http://mydomain/Services/v001.svc/Orders?$filter=close_dt%20gt%201338336000000

FYI: I'm using V2

OrangeDog
  • 36,653
  • 12
  • 122
  • 207
lamarant
  • 3,243
  • 2
  • 25
  • 30
  • 2
    @Rachel I know...I'm asking for the correct syntax to do what I need. The actual link is irrelevant. – lamarant Jan 17 '13 at 21:21

5 Answers5

64

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.
lamarant
  • 3,243
  • 2
  • 25
  • 30
  • the original date is in UTC, so it is always expressed in terms of GMT timezone. Therefore, your local date operations in javascript will implicitly parse the date into your timezone, which is the source of the error. – FlavorScape Mar 15 '16 at 20:13
  • 4
    In my case the ODate V2 implement based on Olingo V2, the function name should be "datetime" rather than "DateTime". – wangf Aug 04 '17 at 06:30
59

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

avitenberg
  • 1,781
  • 19
  • 16
5

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'

Cote Adams
  • 59
  • 1
  • 1
  • 1
    The `datetime` keyword in there worked for me when sending requests to Sharepoint REST endpoint. Thank you. – ericOnline Aug 18 '21 at 19:57
4

Just an FYI: in V3 of the protocol the non-tick-based datetime format is now the default:

http://services.odata.org/Experimental/OData/OData.svc/Products%280%29?$format=application/json;odata=verbose&$select=ReleaseDate

..."ReleaseDate":"1992-01-01T00:00:00"...

Matt Meehan
  • 174
  • 5
0

I will just try to make the answer of @avitenberg more clear:

var date= DateTime.Now;
//Convert time to UTC format
$"filter={close_dt} gt {date:yyyy-MM-ddTHH:mm:ss.FFFZ}";

See Microsoft:

Nb777
  • 1,658
  • 8
  • 27