The !between operator

Learn how to use the !between operator to match the input that is outside of the inclusive range.

Matches the input that is outside of the inclusive range.

!between can operate on any numeric, datetime, or timespan expression.

Syntax

T | where expr !between (leftRange..rightRange)

Parameters

NameTypeRequiredDescription
Tstring✔️The tabular input whose records are to be matched.
exprscalar✔️The expression to filter.
leftRangeint, long, real, or datetime✔️The expression of the left range. The range is inclusive.
rightRangeint, long, real, datetime, or timespan✔️The expression of the right range. The range is inclusive.

This value can only be of type timespan if expr and leftRange are both of type datetime. See example.

Returns

Rows in T for which the predicate of (expr < leftRange or expr > rightRange) evaluates to true.

Examples

Filter numeric values

range x from 1 to 10 step 1
| where x !between (5 .. 9)

Output

x
1
2
3
4
10

Filter datetime

StormEvents
| where StartTime !between (datetime(2007-07-27) .. datetime(2007-07-30))
| count

Output

Count
58590

Filter datetime using a timespan range

StormEvents
| where StartTime !between (datetime(2007-07-27) .. 3d)
| count

Output

Count
58590