BETWEEN Operator

Use BETWEEN to check whether values fall within a given range. BETWEEN accepts numeric, string, and date data types, and can be used with aggregate, window, and math functions.

BETWEEN takes this syntax.

This example filters results on flights whose prices are between $300 and $600.

This example checks whether the sum of profits for each account is less than 5% of the total sum of profits for all accounts. It returns the account name, account ID, and IsLowPercent, a boolean value that is true if the sum of profits is less than 5%.

This example returns ten CloseDate values that are between two TIMESTAMP values, 2014-12-31 and 2015-12-31.

This example uses SQL for CRM Analytics’s EXTRACT function to access the MONTH values from the CloseDate field and return dates between April (4) and July (7).

By default, the BETWEEN statement evaluates a value that falls between the lower and upper bounds in order of least to greatest as true. If the lower and upper bound values are reversed, BETWEEN evaluates the statement as false. To specify this behavior, use the ASYMMETRIC operator.

Let’s go back to the numeric example.

Here, the range is specified as ASYMMETRIC BETWEEN 300 and 600. The statement evaluates to true.

When the range is specified as ASYMMETRIC BETWEEN 600 and 300, the statement evaluates to false.

By using the SYMMETRIC operator, the order of the upper and lower bounds of your BETWEEN statement remain true if reversed. The order is irrelevant.

By using SYMMETRIC, the BETWEEN statement evaluates to true.

Here’s the date example using EXTRACT and the SYMMETRIC operator. By including SYMMETRIC, the query returns the same results, even though the months are filtered BETWEEN 7 AND 4.