Between

The between predicate specifies inclusive bounds for values in a where clause.

Syntax

 
    select * from ... 
    where val between lower_bound and upper_bound
     

This expression is equivalent to:

 
    select * from ... 
    where val >= lower_bound and val <= upper_bound
     

The not operator can be added to an expression using between:

 
    select * from ... 
    where val not between lower_bound and upper_bound
     

which is equivalent to:

 
    select * from ... 
    where val < lower_bound or val > upper_bound
     

Examples

 
    create table t(i integer, d date);
    insert into t values([3,5],['2017-01-01','2017-02-01']);
 
    select * from t where i between 1 and 3;
    i       d
    ------------------------------------------------------------------------------
    3       01/01/2017 00:00:00
 
    Selected records: 1
     
    select * from t where d between '2017-01-01' and '2017-01-30';
    i d
    ------------------------------------------------------------------------------
    3 01/01/2017 00:00:00
 
    Selected records: 1
     
    select * from t where d between '2017-01-01' and now;
    i d
    ------------------------------------------------------------------------------
    3 01/01/2017 00:00:00
    5 02/01/2017 00:00:00
     
    Selected records: 2
     

For more examples please see xSQL SDK sample between.