Query syntax reference
Values (literals)
| Name | Examples | Meaning |
|---|---|---|
| boolean | TRUE / FALSE | True or false |
| null | NULL | Missing or undefined |
| string |
'abc' 'Jim\'s iPhone' |
String of text |
| number | 42 / 2.4 | Number |
| datetime | 2021-03-24 / 2020-01-16T13:27:44 | UTC time stamp (ISO 8601 format) |
| duration | 30d, 2m, 3y6mo | Period of time |
Duration syntax
A duration is a period of time defined in units. More than one unit can be used in a single duration.
| Unit | Long (plural) | Long | Short (plural) | Short | Shortest | Notes |
|---|---|---|---|---|---|---|
| year | years | year | yrs | yr | y | 365 days (leap years are ignored) |
| month | months | month | mos | mo | mo | 30 days (so 12 months != 1 year) |
| week | weeks | week | wks | wk | w | 7 days |
| day | days | day | days | day | d | 24 hours |
| hour | hours | hour | hrs | hr | h | |
| minute | minutes | minute | mins | min | m | |
| second | seconds | second | secs | sec | s | |
| millisecond | milliseconds | millisecond | msecs | msec | ms | 1000 ms = 1 sec |
Duration examples
| Example | Meaning | As a C# TimeSpan |
|---|---|---|
| 2yr3month | 2 years, 3 months | 820:00:00:00.000 |
| 6mo5d2hour | 6 months, 5 days, 2 hours | 185:02:00:00.000 |
| 30d | 30 days | 30:00:00:00.000 |
| 1d12h | 1 day, 12 hours | 1:12:00:00.000 |
| 3h5m221ms | 3 hours, 5 minutes, 221 milliseconds | 05:00.2 |
Comparison operators
| Name | Syntax | Values | Meaning |
|---|---|---|---|
| equal | expression = expression | any | True if the expressions represent equal values |
| not equal | expression != expression | any | True if the expressions represent unequal values |
| less than | expression < expression |
numeric |
True if the first expression represents a lesser value than the second |
| less than or equal | expression <= expression | numeric datetime duration |
True if the first expression represents a value less than or equal to the second |
| greater than | expression > expression | numeric datetime duration |
True if the first expression represents a greater value than the second |
| greater than or equal | expression >= expression | numeric datetime duration |
True if the first expression represents a value greater than or equal to the second |
| contains | expression IN expression | string | True if the first expression represents a value containing the second |
| not contains | expression NOT IN expression | string | True if the first expression represents a value not containing the second |
Logical operators
| Syntax | Meaning | Example |
|---|---|---|
| condition AND condition | Evaluates to TRUE if both conditions are TRUE | AssetTag = 'ABCD' AND OperatingSystem.Name = 'Android' |
| condition OR condition | Evaluates to TRUE if either condition is TRUE | AssetTag = 'ABCD' OR OperatingSystem.Name = 'Android' |
| NOT condition | Evaluates to TRUE if the condition is false | NOT AssetTag = 'ABCD' |
Mathematical operators
| Name | Syntax | Result |
|---|---|---|
| Add | number + number | number |
| datetime + duration | datetime | |
| duration + duration | duration | |
| Subtract | number - number | number |
| datetime - datetime | duration | |
| datetime - duration | datetime | |
| Multiply | number * number | number |
| Divide | number / number | number |
| Modulo | number % number | number |
| Power | number ^ number | number |
Functions
| Syntax | Returns | Meaning |
|---|---|---|
| ABS(number) | number | Gets the absolute value of a number |
| ROUND(number, number N) | number | Rounds a number to N decimal places |
| CEILING(number) | number | Rounds a number up to the nearest integer |
| FLOOR(number) | number | Rounds a number down to the nearest integer |
| UPPER(string) | string | Converts a string to upper-case |
| LOWER(string) | string | Converts a string to lower-case |
| TRIM(string) | string | Removes white space from the beginning and end of a string |
| TRIM_START(string) | string | Removes white space from the beginning of a string |
| TRIM_END(string) | string | Removes white space from the end of a string |
| STARTS_WITH(string, string) | string | Gets whether a string starts with a prefix |
| ENDS_WITH(string, string) | string | Gets whether a string ends with a suffix |
| CONCAT(string, string) | string | Combines two strings |
| SUBSTRING(string, number N) | string | Gets the first N characters of a string |
| SUBSTRING(string, number S, number N) | string | Gets the first N characters of a string after skipping S characters |
| NOW() | datetime | Gets current datetime |
| DATE(datetime) | datetime | Gets date part of datetime |
| TIME(datetime) | duration | Gets time part of datetime |
| YEAR(datetime) | number | Gets year part of datetime |
| MONTH(datetime) | number | Gets month part of datetime |
| DAY(datetime) | number | Gets day part of datetime |
| HOUR(datetime) | number | Gets hour part of datetime |
| MINUTE(datetime) | number | Gets minute part of datetime |
| SECOND(datetime) | number | Gets second part of datetime |
| MILLISECOND(datetime) | number | Gets millisecond part of datetime |
| DAY_OF_WEEK(datetime) | number | Gets the day of the week represented by a datetime |
| DAY_OF_YEAR(datetime) | number | Gets the day of the year represented by a datetime |
| WEEK_OF_YEAR(datetime) | number | Gets the week of the year represented by a datetime |
| START_OF_DAY(datetime) | datetime | Gets the start of the day represented by a datetime |
| END_OF_DAY(datetime) | datetime | Gets the end of the day represented by a datetime |
| START_OF_WEEK(datetime) | datetime | Gets the start of the week represented by a datetime |
| END_OF_WEEK(datetime) | datetime | Gets the end of the week represented by a datetime |
| START_OF_MONTH(datetime) | datetime | Gets the start of the month represented by a datetime |
| END_OF_MONTH(datetime) | datetime | Gets the end of the month represented by a datetime |
| START_OF_YEAR(datetime) | datetime | Gets the start of the year represented by a datetime |
| END_OF_YEAR(datetime) | datetime | Gets the end of the year represented by a datetime |