Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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?

share|improve this question

4 Answers 4

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

share|improve this answer
    
Thanks for your answer, and this would probably work, but I basically gave up on using SharePoints ugly date fields for anything when I figured out that I can treat them as strings, as indicated in my answer. –  Chris Jaynes Sep 28 '12 at 21:51
up vote 0 down vote accepted

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.

share|improve this answer

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';
    };
}
share|improve this answer
    
Hmm. Although I agree that you've boiled the syntax down to something that's a bit more manageable, it still seems strange to have to convert back and forth from dates to strings to dates. Since the data has to cross the http wire as a string, and I can't manipulate it server side, why wouldn't I just use a column that's a more friendly string format? If I just need to be able to compare these dates via really simple queries, why should I keep converting them back and forth? What does it buy me? Thanks for your answer! I'm curious to explore this idea a bit more... –  Chris Jaynes Oct 1 '12 at 13:46
    
I understand your frustration about this syntax. But how do you create this filterValue from a date? Do you have to convert it in javascript by using a custom format procedure? In the solution I showed you don't have to care about formatting, you work only with Date objects the whole way. Suppose: you have a datepicker: a user provides a date and you have to put it into the request. Here you no longer need to format it "manually", just create a standard ISO format by running date.toISOString(). In my opinion, it is less error prone. –  Anatoly Mironov Oct 2 '12 at 5:21
    
I updated the post to show that I actually am using the ISO format. I like a library called Moment.js for dates. I tried using the SP date field as you describe, but it had all kinds of problems with inconsistencies in time zones. I guess what bugs me most about using the built-in Date format is that you receive the date in one format, and the queries use a different format. This is silly and error-prone. I'm going to stick with using my own date fields and Moment.js, which has no problems parsing it and formatting using the ISO standard on both sides. –  Chris Jaynes Oct 2 '12 at 14:58
    
Sounds good! Thanks for the discussion! –  Anatoly Mironov Oct 2 '12 at 15:43

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!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.