date_timestamp_expression can be a constant, scalar UDF, scalar subquery, or business calendar function that evaluates to a date or timestamp value.
The data type of date_time_expression must be comparable with TIMESTAMP(6) WITH TIME ZONE. The following rules apply.
|Data Type of date_time_expression||Details|
|DATE||The DATE value is cast to TIMESTAMP(6) WITH TIME ZONE and used for qualification. The time portion of the converted timestamp value is 00:00:00 (hh:mi:ss) in the session time zone. The row is qualified based on the UTC timestamp values.|
|TIMESTAMP[(n)] [WITH TIME ZONE]||If the timestamp value has a coarser precision, it is converted to TIMESTAMP(6) WITH TIME ZONE and then the rows are qualified.|
|Any other data type||The system reports an error.|
References to a transaction-time column can appear anywhere in the scope of the query and anywhere in the query block, including a WHERE condition or JOIN condition.
The AS OF qualifier serves as an additional qualification criteria such that only rows with a transaction-time value that overlaps the given time are eligible to participate in the query. Thereafter, the query treats all the underlying tables as non-transaction-time tables. Operations such as joins, aggregation, set operations, and so forth are not impacted by this qualifier.
If date_timestamp_expression in the transaction-time dimension uses TEMPORAL_DATE or TEMPORAL_TIMESTAMP, the value of the built-in function evaluates to the time of the transaction. If date_timestamp_expression is a value that is in the future, the qualifier is as if it is current in the transaction-time dimension.