A special case of column comparisons in the One Identity Manager query language are date difference comparisons. These compare the value of a column with a time range based on the current time.
WHERE <Column> <Operator> DATE <Integer> <Unit> AGO
Years |
YEARS YEAR Y |
Months |
MONTHS MONTH M |
Weeks |
WEEKS WEEK |
Days |
DAYS DAY D |
Hours |
HOURS HOUR H |
Minutes |
MINUTES MINUTE MIN |
Seconds |
SECONDS SECOND S |
Milliseconds |
MILLISECONDS MILLISECOND MS |
Examples
WHERE XDateInserted < DATE 3 MONTHS AGO
WHERE XDateInserted > DATE 5 MIN AGO
Comparisons of date ranges in the One Identity Manager query language check whether a date falls within a given date range.
Syntax for named ranges
WHERE <Column> IN RANGE <Range name> [ TIMEZONE 'Timezone ID'] [CULTURE 'Culture ID']
Syntax for a specific time range
WHERE <Column> IN RANGE <Start time> TO <End time> [TIMEZONE <Timezone ID>]
Area start time is included, end time is excluded.
Permitted identifiers are:
TODAY
YESTERDAY
THIS WEEK
THIS MONTH
THIS YEAR
LAST WEEK
LAST MONTH
LAST YEAR
LAST <integer> DAYS
LAST <integer> DAY
The names of the time zones must match the identifiers on the system running the query. If no time zone is given, UTC is used.
The culture indicates which day starts the week. It is only useful if one of the week ranges is specified.
Examples
WHERE XDateInserted IN RANGE YESTERDAY
WHERE XDateInserted IN RANGE YESTERDAY
TIMEZONE 'W. Europe Standard Time'
WHERE XDateInserted IN RANGE LAST YEAR
WHERE XDateInserted IN RANGE THIS WEEK
TIMEZONE 'W. Europe Standard Time'
CULTURE 'de-DE'
WHERE XDateInserted IN RANGE 2020-01-01 TO 2020-02-01
TIMEZONE 'W. Europe Standard Time'
Related topics
In the One Identity Manager query language, comparisons between fixed values support only the operators equal to (=) and not equal to (<>).
Example
FROM Person
WHERE 1 = 0
SELECT DISPLAYS
In the One Identity Manager query language, parameters can only be compared against fixed values. All comparisons that are valid for columns can also be used for parameters.
NOTE: IN clauses and NOT IN clauses cannot be used.
Examples
WHERE @StringParameter = ''
WHERE @IntParameter > 5
WHERE @IntParameter BITSSET 4
Related topics