This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Between operators

1 - 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

2 - The between operator

Learn how to use the between operator to return a record set of values in an inclusive range for which the predicate evaluates to true.

Filters a record set for data matching the values in an 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. For example, the table name.
exprscalar✔️The expression used 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 and expr <= rightRange) evaluates to true.

Examples

Filter numeric values

range x from 1 to 100 step 1
| where x between (50 .. 55)

Output

x
50
51
52
53
54
55

Filter by date

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

Output

Count
476

Filter by date and time

StormEvents
| where StartTime between (datetime(2007-12-01T01:30:00) .. datetime(2007-12-01T08:00:00))
| count

Output

Count
301

Filter using a timespan range

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

Output

Count
476