Comparison
Operator | Description | Examples |
= (equal) | TRUE if both expressions are equal. FALSE if either or both expressions are null. | 1 = 1 → TRUE ‘A’ = ‘A’ → TRUE 1/1/22 = 1/1/22→ TRUE 1 = 2 → FALSE |
!= (not equal) | TRUE if both expressions are not equal. FALSE if either or both expressions are null. | 1 != 2 → TRUE ‘A’ != ‘B’ → TRUE 1/1/22 != 1/2/22→ TRUE 1 != 1 → FALSE |
> (greater than) | TRUE if the left expression is greater than the right expression. FALSE if either or both expressions are null. | 2 > 1 → TRUE ‘B’ > ‘A’ → TRUE 1/2/22 > 1/1/22→ TRUE 1 > 1 → FALSE |
>= (greater than or equal) | TRUE if the left expression is greater than or equal to the right expression. FALSE if either or both expressions are null. | 2 >= 1 → TRUE ‘B’ >= ‘A’ → TRUE 1/2/22 >= 1/1/22→ TRUE 1 >= 1 → TRUE |
< (less than ) | TRUE if the left expression is less than the right expression. FALSE if either or both expressions are null. | 1 < 2 → TRUE ‘A’ < ‘B’ → TRUE 1/1/22 < 1/2/22→ TRUE 1 < 1 → FALSE |
<= (less than or equal) | TRUE if the left expression is less than or equal to the right expression. FALSE if either or both expressions are null. | 1 <= 2 → TRUE ‘A’ <= ‘B’ → TRUE 1/1/22 <= 1/2/22→ TRUE 1 <= 1 → TRUE |
Logical
NOT | Reverses the value of a Boolean operator. | NOT TRUE → FALSE |
AND | TRUE if both Boolean expressions are TRUE. FALSE if either or both boolean expressions are null. | TRUE AND TRUE → TRUE TRUE AND FALSE → FALSE |
OR | TRUE if either Boolean expression is TRUE. FALSE if either or both boolean expressions are null. | TRUE OR TRUE → TRUE TRUE OR FALSE → TRUE |
IS NULL | TRUE if the expression is null | NULL IS NULL → TRUE 1 IS NULL → FALSE |
IS NOT NULL | TRUE if the expression is not null | NULL IS NOT NULL → FALSE 1 IS NOT NULL → TRUE |
CASE WHEN … THEN .. WHEN … THEN … … ELSE … END | Evaluates each WHEN condition and returns the THEN value for the first WHEN condition that is met. Optional ELSE statement.
Similar to IF / THEN / ELSE in our systems. | CASE WHEN 0 = 1 THEN “A” WHEN 1 = 1 THEN “B” ELSE “C” END |
Mathematical
+ (addition) | Adds two numbers; or adds duration to a date/datetime.
Duration types include SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR. | 1 + 2 → 3
1/1/22 + INTERVAL 1 DAY → 1/2/22
1/1/22 1:30:01 + INTERVAL 1 HOUR → 1/1/22 2:30:01 |
- (subtraction) | Subtracts two numbers; subtracts two dates or subtracts duration from a date/datetime.
Duration types include SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR. | 2 - 1 → 1
‘1/2/22’ - ‘1/1/22’ → ‘1 DAY’
‘1/2/22’ - INTERVAL 1 DAY → ‘1/1/22’
‘1/1/22 1:30:01’ - INTERVAL 1 HOUR → ‘1/1/22 0:30:01’ |
- (negative) | Returns the negative of the value of a numeric expression | -(1) → -1 -(-1) → 1 |
* (multiplication) | Multiplies two numbers | 1 * 2 → 2 |
/ (division) | Divides two numbers | 1 / 2 → 0.5 |
Date
INTERVAL | Apply a duration to a date or datetime. Similar to the common DateAdd() function.
Duration types include SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR. | 1/1/22 + INTERVAL 1 DAY → 1/2/22
1/1/22 1:30:01 + INTERVAL 1 HOUR → 1/1/22 2:30:01 |