REST service $filter Date column

Question

Using the REST services, I'm trying to filter by the Created column, which is a Date data type. When trying to $filter on that column, I passed in the same date format that comes back in the JSON result: "/Date(1348748902000)/", so my URL looks like this:

http://hostname/_vti_bin/listdata.svc/MyList?$filter=Created%20gt%20'/Date(1348748902000)/'

Unfortunately, while this works for simple string fields, I get an error back when I try this for date fields:

Operator 'gt' incompatible with operand types 
System.Nullable`1[[System.DateTime, mscorlib, Version=2.0.0.0, Culture=neutral, 
PublicKeyToken=b77a5c561934e089]]' and 'System.String' at position 8.

I can't find anything in MSDN or elsewhere. Anyone have any ideas?

Answer 1

Per Jakobsen has a very nice solution. You don't need to "muck up" anything, Chris! The only thing I have to add is to show how the REST url can be automated. Look at this:

var date = new Date(), //or some relevant date
    filterFormat = "$filter={0} gt datetime'{1}'",
    filterQuery = String.format(filterFormat, "Created", date.toISOString()),
    url = "http://hostname/_vti_bin/listdata.svc/MyList" + "?" + filterQuery;

So actually the format is not so weird: just so "$filter={0} gt datetime'{1}'". The rest should be put in automatically with String.format and Date.toISOString()

Date.toISOString will convert the date in the desired format. To be sure it works in older browsers, just add this fallback:

if (!Date.prototype.toISOString) {
    Date.prototype.toISOString = function() {
        function pad(n) { return n < 10 ? '0' + n : n }
        return this.getUTCFullYear() + '-'
            + pad(this.getUTCMonth() + 1) + '-'
            + pad(this.getUTCDate()) + 'T'
            + pad(this.getUTCHours()) + ':'
            + pad(this.getUTCMinutes()) + ':'
            + pad(this.getUTCSeconds()) + 'Z';
    };
}

Answer 2

The syntax is like this $filter=Created%20gt%20datetime'2012-01-01T00%3A00%3A00' (for > Jan. 1st 2012)

Answer 3

Rather than arguing with SharePoint about date formats, which was proving to be quite a tedious dicsussion, I ended up mirroring the Created date into a calculated Timestamp field that's configured like this:

=TEXT(Created,"yyyy-mm-ddThh:mm:ss")

This Timestamp column ends up being a string value that's easier to deal with on pretty much all levels.

I don't know if the datetime format in Per Jakobsen's answer would work, but I'd rather not muck up those already UGLY SP urls any worse than I have to.

EDIT: As discussed in the comments on the other answers, and after attempting those solutions, I've determined that it's not a good idea for SP to be sending the date down in the ASP.Net format, but then requires us to compare dates using the ISO format. This solution allows the ISO format to be used both ways, and eliminated issues I was having with the Time Zones not parsing back and forth properly across formats.

Thanks to the other folks who provided potential solutions. Hopefully this discussion will be of use to others.

Answer 4

I also faced a similar issue when I wanted to filter by the Modified field. The following method i developed worked for me: If you are using REST API the datetime literal is used as, e.g. datetime'2013-03-12T12:01:38Z' or datetime'2013-03-12′. Therefore I needed the date in the format yyyy-mm-dd

Here is the Code:

var d = new Date();               
var date = convertToDate(d);

//querypath to be used as url when making an ajax call
var querypath = "http://farmname.com/site/_vti_bin/listdata.svc/listName?$filter=Modified gt datetime'"+date+"'";

function convertToDate(date) {

 var mnth = date.getMonth() + 1;

 var day = "";
//appending 0 for day less than 10 days since getDate() will for instance return 02 or 03 as just 2 or 3

 if(date.getDate()<10){
        day = "0"+date.getDate().toString();
        else day = date.getDate().toString();
        return date.getFullYear() + "-" + mnth + "-" + day;
            }

I hope this will help!