diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-between-join_01.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-between-join_01.sqlpp
new file mode 100644
index 0000000..106fc9d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-between-join_01.sqlpp
@@ -0,0 +1,37 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop dataverse test1 if exists;
+create dataverse test1;
+use test1;
+
+create type TestType as open {
+    key1: int32,
+    key2: int32,
+    fname : string,
+    lname : string
+}
+
+create dataset DsOne(TestType) primary key key1;
+create dataset DsTwo(TestType) primary key key1;
+
+FROM DsOne x, DsTwo y
+WHERE x.key2 /*+ indexnl */ BETWEEN y.key1 AND 10
+SELECT VALUE x
+;
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-between-join_01.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-between-join_01.plan
new file mode 100644
index 0000000..05af741
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-between-join_01.plan
@@ -0,0 +1,13 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- BTREE_SEARCH  |PARTITIONED|
+          -- BROADCAST_EXCHANGE  |PARTITIONED|
+            -- STREAM_SELECT  |PARTITIONED|
+              -- ASSIGN  |PARTITIONED|
+                -- STREAM_PROJECT  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- DATASOURCE_SCAN  |PARTITIONED|
+                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/comparison/datetime_range_between/datetime_range_between.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/comparison/datetime_range_between/datetime_range_between.1.ddl.sqlpp
new file mode 100644
index 0000000..20aff6d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/comparison/datetime_range_between/datetime_range_between.1.ddl.sqlpp
@@ -0,0 +1,35 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+
+create type Tweet as
+ closed {
+  id : int64,
+  tweetid : int64,
+  loc : point,
+  time : datetime,
+  text : string
+}
+
+create dataset TwitterData(Tweet) primary key id;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/comparison/datetime_range_between/datetime_range_between.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/comparison/datetime_range_between/datetime_range_between.2.update.sqlpp
new file mode 100644
index 0000000..f9d5e21
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/comparison/datetime_range_between/datetime_range_between.2.update.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use test;
+
+
+load  dataset TwitterData using localfs ((`path`=`asterix_nc1://data/twitter/smalltweets.txt`),(`format`=`adm`))
+pre-sorted;
+
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/comparison/datetime_range_between/datetime_range_between.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/comparison/datetime_range_between/datetime_range_between.3.query.sqlpp
new file mode 100644
index 0000000..42ce98c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/comparison/datetime_range_between/datetime_range_between.3.query.sqlpp
@@ -0,0 +1,27 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use test;
+
+
+select element {'id':t.id}
+from  TwitterData as t
+where t.time between datetime('2011-05-15T16:00:00Z') and datetime('2011-05-15T21:59:59Z')
+order by t.id
+;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/comparison/int_not_between/int_not_between.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/comparison/int_not_between/int_not_between.3.query.sqlpp
new file mode 100644
index 0000000..913d394
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/comparison/int_not_between/int_not_between.3.query.sqlpp
@@ -0,0 +1,23 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+SELECT VALUE x
+FROM  [1,3,2] x
+WHERE x NOT BETWEEN 2 AND 3
+;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/comparison/int_not_between/int_not_between.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/comparison/int_not_between/int_not_between.1.adm
new file mode 100644
index 0000000..d00491f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/comparison/int_not_between/int_not_between.1.adm
@@ -0,0 +1 @@
+1
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
index d5fe65b..a074df9 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
@@ -933,6 +933,11 @@
       </compilation-unit>
     </test-case>
     <test-case FilePath="comparison">
+      <compilation-unit name="datetime_range_between">
+        <output-dir compare="Text">datetime_range</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="comparison">
       <compilation-unit name="datetime_tzeq">
         <output-dir compare="Text">datetime_tzeq</output-dir>
       </compilation-unit>
@@ -1053,6 +1058,11 @@
       </compilation-unit>
     </test-case>
     <test-case FilePath="comparison">
+      <compilation-unit name="int_not_between">
+        <output-dir compare="Text">int_not_between</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="comparison">
       <compilation-unit name="lt_01">
         <output-dir compare="Text">lt_01</output-dir>
       </compilation-unit>
diff --git a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/struct/OperatorType.java b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/struct/OperatorType.java
index a5a1bb8..f00d3ca 100644
--- a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/struct/OperatorType.java
+++ b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/struct/OperatorType.java
@@ -43,7 +43,9 @@
     LIKE("like"),
     NOT_LIKE("not_like"),
     IN("in"),
-    NOT_IN("not_in");
+    NOT_IN("not_in"),
+    BETWEEN("between"),
+    NOT_BETWEEN("not_between");
 
     private final String symbol;
 
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/OperatorExpressionVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/OperatorExpressionVisitor.java
index bbe7c27..60c4aa0 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/OperatorExpressionVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/OperatorExpressionVisitor.java
@@ -36,6 +36,7 @@
 import org.apache.asterix.lang.common.struct.OperatorType;
 import org.apache.asterix.lang.common.struct.QuantifiedPair;
 import org.apache.asterix.lang.sqlpp.visitor.base.AbstractSqlppExpressionScopingVisitor;
+import org.apache.hyracks.algebricks.core.algebra.expressions.IExpressionAnnotation;
 
 public class OperatorExpressionVisitor extends AbstractSqlppExpressionScopingVisitor {
 
@@ -64,6 +65,9 @@
             case CONCAT:
                 // There can be multiple "||"s in one operator expression (according to the grammar).
                 return processConcatOperator(operatorExpr);
+            case BETWEEN:
+            case NOT_BETWEEN:
+                return processBetweenOperator(operatorExpr, opType);
             default:
                 break;
         }
@@ -103,4 +107,37 @@
         return new CallExpr(new FunctionSignature(null, CONCAT, 1), operatorExpr.getExprList());
     }
 
+    private Expression processBetweenOperator(OperatorExpr operatorExpr, OperatorType opType) throws AsterixException {
+        // The grammar guarantees that the BETWEEN operator gets exactly three expressions.
+        Expression target = operatorExpr.getExprList().get(0);
+        Expression left = operatorExpr.getExprList().get(1);
+        Expression right = operatorExpr.getExprList().get(2);
+
+        // Creates the expression left <= target.
+        Expression leftComparison = createLessThanExpression(left, target, operatorExpr.getHints());
+        // Creates the expression target <= right.
+        Expression rightComparison = createLessThanExpression(target, right, operatorExpr.getHints());
+        OperatorExpr andExpr = new OperatorExpr();
+        andExpr.addOperand(leftComparison);
+        andExpr.addOperand(rightComparison);
+        andExpr.addOperator("and");
+        return opType == OperatorType.BETWEEN ? andExpr :
+                new CallExpr(new FunctionSignature(null, "not", 1),
+                        new ArrayList<>(Collections.singletonList(andExpr)));
+    }
+
+    private Expression createLessThanExpression(Expression lhs, Expression rhs, List<IExpressionAnnotation> hints)
+            throws AsterixException {
+        OperatorExpr comparison = new OperatorExpr();
+        comparison.addOperand(lhs);
+        comparison.addOperand(rhs);
+        comparison.addOperator("<=");
+        if (hints != null) {
+            for (IExpressionAnnotation hint : hints) {
+                comparison.addHint(hint);
+            }
+        }
+        return comparison;
+    }
+
 }
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj b/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
index 3d8c4d9..2f738ff 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
+++ b/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
@@ -1750,7 +1750,7 @@
   IExpressionAnnotation annotation = null;
 }
 {
-    operand = IsExpr()
+    operand = BetweenExpr()
     {
       if (operand instanceof VariableExpr) {
         String hint = getHint(token);
@@ -1788,7 +1788,7 @@
           }
         }
 
-       operand = IsExpr()
+       operand = BetweenExpr()
       {
          broadcast = false;
          if (operand instanceof VariableExpr) {
@@ -1809,6 +1809,63 @@
      }
 }
 
+Expression BetweenExpr()throws ParseException:
+{
+  boolean not = false;
+  OperatorExpr op = null;
+  Expression operand = null;
+  IExpressionAnnotation annotation = null;
+}
+{
+    operand = IsExpr()
+    (
+      LOOKAHEAD(2)
+      (<NOT> { not = true; })? <BETWEEN>
+        {
+          String mhint = getHint(token);
+          if (mhint != null) {
+            if (mhint.equals(INDEXED_NESTED_LOOP_JOIN_HINT)) {
+                annotation = IndexedNLJoinExpressionAnnotation.INSTANCE;
+            } else if (mhint.equals(SKIP_SECONDARY_INDEX_SEARCH_HINT)) {
+                annotation = SkipSecondaryIndexSearchExpressionAnnotation.INSTANCE;
+            }
+          }
+          String operator = token.image.toLowerCase();
+          if(not){
+            operator = "not_" + operator;
+          }
+          if (op == null) {
+            op = new OperatorExpr();
+            op.addOperand(operand);
+            op.setCurrentop(true);
+          }
+          try{
+            op.addOperator(operator);
+          } catch (AsterixException e){
+            throw new ParseException(e.getMessage());
+          }
+        }
+
+       operand = IsExpr()
+       {
+         op.addOperand(operand);
+       }
+
+       <AND>
+       operand = IsExpr()
+       {
+         op.addOperand(operand);
+       }
+    )?
+
+    {
+       if (annotation != null) {
+         op.addHint(annotation);
+       }
+       return op==null? operand: op;
+    }
+}
+
 Expression IsExpr() throws ParseException:
 {
     Expression expr = null;