BETWEEN( ) function

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

Syntax

BETWEEN(value, min, max)

Parameters

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

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

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")

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.