BETWEEN( ) function

Returns a logical value indicating whether the specified value falls within a range.

Syntax

BETWEEN(value, min, max)

Parameters

Name Type Description
value

character

numeric

datetime

 The field, expression, or literal value to test.
min

character

numeric

datetime

The minimum value of the range.

Can be a field, expression, or literal value.
max

character

numeric

datetime

The maximum value of the range.

Can be a field, expression, or literal value.

Note

The range evaluating to T (true) includes the min and max values.

For information regarding character ranges, see SET EXACT behavior.

Output

Logical. Returns T (true) if value is greater than or equal to the min value, and less than or equal to the max value. Returns F (false) otherwise.

Examples

Basic examples

Numeric input

Returns T:

BETWEEN(500,400,700)

Returns F:

BETWEEN(100,400,700)

Character input

Returns T:

BETWEEN("B","A","C")

Returns F, because the character comparison is case-sensitive, and lowercase "b" does not fall between uppercase "A" and "C":

BETWEEN("b","A","C")

Datetime input

Returns T:

BETWEEN(`141230`,`141229`,`141231`)

Returns T for all values in the Login_time field from 07:00:00 AM to 09:00:00 AM, inclusive, and F otherwise:

BETWEEN(Login_time,`t070000`,`t090000`)

SET EXACT behavior

Returns T for all values in the Last_Name field that begin with the letters from "C" to "K", inclusive, and F otherwise (SET EXACT must be OFF):

BETWEEN(Last_Name, "C", "K")

Returns T for all values in the Last_Name field that begin with the letters from "C" to "J", inclusive, and F otherwise (SET EXACT must be ON). Also returns T for the single letter "K":

BETWEEN(Last_Name, "C", "K")

Field input

Returns T for all values in the Invoice_Date field from 30 Sep 2014 to 30 Oct 2014, inclusive, and F otherwise:

BETWEEN(Invoice_Date, `20140930`, `20141030`)

Returns T for all records in which the invoice date does not fall between the PO date and the paid date, inclusive, and F otherwise:

NOT BETWEEN(Invoice_Date, PO_Date, Paid_Date)

Returns T for all values in the Invoice_Amount field from $1000 to $5000, inclusive, and F otherwise:

BETWEEN(Invoice_Amount, 1000, 5000)

Advanced examples

Create a filter to view a salary range

The following example opens the Employee_List sample table and applies a filter that limits the records displayed to include only employees that earn a salary greater than or equal to $40,000.00, and less than or equal to $50,000.00.

OPEN Employee_List
SET FILTER TO BETWEEN(Salary, 40000.00, 50000.00)

Create a filter to find dates within a range that changes

You have created a table that joins data from your company's travel and expense system with company credit card data. You want to find any instances where an employee was reimbursed for a hotel room charge that was also charged to the company credit card.

You join the two sets of data on the Amount field and plan to use the dates of the hotel stay and the T&E expense date to confirm that the two amounts refer to the same expense. The problem is that the date in the T&E system can differ by a day or two from the hotel dates in the company credit card data.

The example below opens the Joined_expense_data table and applies a filter that finds T&E dates within a range of hotel room dates. By using fields rather than actual date values, the ranges shift with the data.

OPEN Joined_expense_data
SET FILTER TO BETWEEN(T_E_date, Arrival_date-2, Arrival_date+2) OR BETWEEN(T_E_date, Departure_date-2, Departure_date+2)

Remarks

Supported data types

Inputs to the BETWEEN( ) function can be numeric, character, or datetime data. You cannot mix data types. All three inputs must belong to the same data category.

Use BETWEEN( ) instead of the AND operator

You can use the BETWEEN( ) function instead of expressions that use the AND operator.

For example:

BETWEEN(Invoice_Amount, 1000, 5000)

is equivalent to

Invoice_Amount >= 1000 AND Invoice_Amount <= 5000

The order of min and max

The order of min and max in the BETWEEN( ) function does not matter because Analytics automatically identifies which value is the minimum and which value is the maximum.

Both of the examples below return T:

BETWEEN(2500, 1000, 5000)
BETWEEN(2500, 5000, 1000)

Decimal precision of numeric inputs

When the numeric inputs being compared have a different decimal precision, the comparison uses the higher level of precision.

Returns T, because 1.23 is equal to 1.23:

BETWEEN(1.23, 1.23, 1.25)

Returns F, because 1.23 is less than 1.234 once the third decimal place is considered:

BETWEEN(1.23, 1.234, 1.25)

Character data

Case sensitivity

The BETWEEN( ) function is case-sensitive when used with character data. When it compares characters, "a" is not equivalent to "A".

Returns F:

BETWEEN("B","a","C")

If you are working with data that includes inconsistent case, you can use the UPPER( ) function to convert the values to consistent case before using BETWEEN( ).

Returns T:

BETWEEN(UPPER("B"), UPPER("a"), UPPER("C"))

Partial matching

Partial matching is supported for character comparisons.

value can be contained by min.

Returns T, even though value "AB" appears to be less than min "ABC":

BETWEEN("AB", "ABC", "Z")

max can be contained by value.

Returns T, even though value "ZZ" appears to be greater than max "Z":

BETWEEN("ZZ", "ABC", "Z")

Note

The shorter value in the character comparison must appear at the start of the longer value to constitute a match.

Partial matching and SET EXACT

Partial matching is enabled when SET EXACT = OFF, which is the Analytics default setting. If SET EXACT = ON, partial matching is disabled and the comparison values must exactly match to constitute a match.

Both examples above are False when SET EXACT is ON.

For more information about SET EXACT (the Exact Character Comparisons option), see SET command.

Turning SET EXACT off or on

If you want to ensure that the Exact Character Comparisons option is not used with the BETWEEN( ) function, check that the option is deselected in the Table tab in the Options dialog box (Tools > Options).

If you are using a script, you can add the SET EXACT OFF command before the BETWEEN( ) function appears. If required, you can restore the previous state with the SET EXACT ON command.

Datetime parameters

A date, datetime, or time field specified as a function input can use any date, datetime, or time format, as long as the field definition correctly defines the format.

Mixing date, datetime, and time inputs

You are not prevented from mixing date, datetime, and time values in the BETWEEN( ) function's three inputs, but mixing these Datetime subtypes can give results that are not meaningful.

Analytics uses serial number equivalents to process datetime calculations, so even if you are interested in only the date portion of a datetime value, the time portion still forms part of the calculation.

Consider the following examples:

Returns T, because 31 December 2014 falls within the range specified by min and max:

BETWEEN(`20141231`,`20141229`,`20141231`)

Returns F, even though 12:00 PM on 31 December 2014 appears to fall within the range specified by min and max:

BETWEEN(`20141231 120000`,`20141229`,`20141231`)

If we look at the serial number equivalent of these two expressions, we can see why the second one evaluates to false.

Returns T, because the serial number value is equal to the serial number max:

BETWEEN(42003.000000, 42001.000000, 42003.000000)

Returns F, because the serial number value is greater than the serial number max:

BETWEEN(42003.500000, 42001.000000, 42003.000000)

The serial number 42003.500000 is greater than 42003.000000 and therefore is out of range, even though the two dates are identical. 0.500000 is the serial number equivalent of 12:00 PM.

Harmonize Datetime subtypes

To avoid the problems that can be caused by mixing Datetime subtypes, you can use functions to harmonize the subtypes.

For example, this expression, which uses the same initial values as the second example above, returns T rather than F:

BETWEEN(CTOD(DATE(`20141231 120000`,"YYYYMMDD"),"YYYYMMDD"),`20141229`,`20141231`)

Specifying a literal date, datetime, or time value

When specifying a literal date, datetime, or time value for any of the function inputs, you are restricted to the formats in the table below, and you must enclose the value in backquotes – for example, `20141231`.

Do not use any separators such as slashes (/) or colons (:) between the individual components of dates or times.

  • Datetime values – you can use any combination of the date, separator, and time formats listed in the table below. The date must precede the time, and you must use a separator between the two. Valid separators are a single blank space, the letter 't', or the letter 'T'.

  • Time values – you must specify times using the 24-hour clock. Offsets from Coordinated Universal Time (UTC) must be prefaced by a plus sign (+) or a minus sign (-).

    Example formats

    Example literal values

    YYYYMMDD

    `20141231`

    YYMMDD

    `141231`

    YYYYMMDD hhmmss

    `20141231 235959`

    YYMMDDthhmm

    `141231t2359`

    YYYYMMDDThh

    `20141231T23`

    YYYYMMDD hhmmss+/-hhmm

    (UTC offset)

    `20141231 235959-0500`

    YYMMDD hhmm+/-hh

    (UTC offset)

    `141231 2359+01`

    thhmmss

    `t235959`

    Thhmm

    `T2359`

    Note

    Do not use hh alone in the main time format with data that has a UTC offset. For example, avoid: hh+hhmm. Results can be unreliable.