Between Predicate (SQL)

Help Contents

Introduction

The SQL Between Predicate compares one value with two other values returning TRUE, FALSE, or UNKNOWN.

Syntax

between-predicate ::=
  test-expression between-predicate-part2

between-predicate-part2 ::=
  [ NOT ] BETWEEN lower-expression AND upper-expression

Syntax Details

test-expression is the Value Expression being tested.

NOT is an optional keyword that negates the result

lower-expression is a Value Expression that declares the lower limit of test-expression. lower-expression must be the same type as test-expression.

upper-expression is a Value Expression that declares the upper limit of test-expression. upper-expression must be the same type as test-expression.

The between-predicate-part2 can be used as a test in the when-operand of a Case Function.

Remarks

The predicate X BETWEEN Y AND Z is equivalent to X >= Y AND X <= Z.

The predicate X NOT BETWEEN Y AND Z is equivalent to NOT( X >= Y AND X <= Z).

If any of X, Y or Z are NULL, the predicate returns UNKNOWN.

Examples

To find the digital tags that have changed state in the last hour:

SELECT FullName,Value,StateDesc
FROM TagDataPointDigital
WHERE StateChangeTime 
    BETWEEN Current_Timestamp - Interval '1' Hour AND Current_Timestamp

Further Information

Predicates

To learn about other types of predicate.

Fernhill SCADA Structured Query Language (SQL)

To learn more about the SQL features in Fernhill SCADA.

Glossary

For the meaning of terms used in Fernhill SCADA.