REST API's Where clause

Yang Wen asked on September 9, 2016 20:40

I know you can specify simple where clause in your queries against the REST API.

Does it support the keyword BETWEEN? I've tried a bit and could not get it to work.

I want to return from REST a subset of data from the table, dictated by the data's creation date. Thanks

rest where

Correct Answer

Jan Hermann answered on September 10, 2016 08:49

You can use less than and greater than operators:

/rest/cms.user/all?where=lastlogon>'2016-09-09T00:0:00.0000000' and lastlogon<'2016-09-11T00:0:00.0000000'

0 votesVote for this answer Unmark Correct answer

Recent Answers

Yang Wen answered on September 9, 2016 21:47

It looks like TO_DATE is also not available

0 votesVote for this answer Mark as a Correct answer

Zach Perry answered on September 9, 2016 22:17 (last edited on September 9, 2016 22:18)

The documentation says you have to use a where as a querystring:

SQL WHERE condition for filtering the loaded data. The parameter only allows the following types of SQL syntax:
column names, values and basic operators: =, !=, >, <
AND & OR operators, parentheses
column BETWEEN value AND value
column LIKE value
column IN (values)
column IS NULL
NOT keyword for the above expressions (NOT BETWEEN, NOT LIKE, NOT IN, IS NOT NULL)
Other expressions and SQL functions are not supported.
Example: ~/rest/cms.user?Where=UserIsEditor=1

0 votesVote for this answer Mark as a Correct answer

Yang Wen answered on September 9, 2016 22:21

Yes I have successfully applied a WHERE clause in the query string, but Date specific functions seem to be not supported. I see the v9 documentation now explicitly lists the supported SQL Syntax, whereas the v8 doc did not. Thanks

0 votesVote for this answer Mark as a Correct answer

Yang Wen answered on September 10, 2016 00:34

So let me ask a follow up question. Without using the Where clause in URL query string, is there a way to filter what's returned by the REST API based on value in a date column?

0 votesVote for this answer Mark as a Correct answer