Search code examples
restsharepointsharepoint-2010

REST - Date range filter on a singleline text column

Im having a tough time finding details on this. Im using Sharepoint 2010 and using REST to grab my data. I would like to query a date range using REST:

http://localhost/_vti_bin/listdata.svc/TEST?$filter=(Column2 ge datetime'2016-04-06') and (Column2 lt datetime'2016-04-09')

But I am getting an error:

Opérateur 'ge' incompatible avec les types d'opérande 'System.String' et 'System.DateTime' à la position 20.

Sorry it's in french but roughly translates to the 'ge' operator is incompatible with 'System.String' et 'System.DateTime' types.

Here is an example of my columns, they are all single line texts columns. 

Column1    Column2             Column3
BLah       2016-04-01 16:00    Blah1
Blahs      2016-04-02 16:00    Blahs2
Blhass     2016-04-03 16:00    Blahss3
Blhasss    2016-04-06 16:00    Blhasss4
sBlah      2016-04-08 16:00    sBlah5
ssBlah     2016-04-08 16:00    ssBlah6
sssBlah    2016-04-09 16:00    sssBlah7

Would anyone understand with it's doing this?

Solution

  • The reason it's complaining is that the format you are using only works for comparing actual date fields; the fields in your list are single line of text fields.

    Fortunately, you've got your date strings formatted in such a way that their alphabetical order corresponds nicely with their temporal order.

    Therefore, you can perform your same filtering logic to retrieve your range of items as long as you don't try to convert your comparison values to datetimes; leave them as a strings.

    $filter=(Column2 ge '2016-04-06') and (Column2 lt '2016-04-09')