Context functions¶

This family of functions allows for the gathering of information about the context in which the statement is executed. These functions are evaluated at most once per statement.

List of functions¶

Sub-category	Function	Notes
General context	CURRENT_CLIENT
	CURRENT_DATE
	CURRENT_IP_ADDRESS
	CURRENT_REGION
	CURRENT_TIME
	CURRENT_TIMESTAMP
	CURRENT_VERSION
	GETDATE	Alias for CURRENT_TIMESTAMP.
	LOCALTIME	Alias for CURRENT_TIME.
	LOCALTIMESTAMP	Alias for CURRENT_TIMESTAMP.
	SYSDATE
	SYSTIMESTAMP
Session context	ALL_USER_NAMES
	CURRENT_ACCOUNT	Returns account locator.
	CURRENT_ACCOUNT_NAME	Returns account name.
	CURRENT_ORGANIZATION_NAME
	CURRENT_ROLE
	CURRENT_AVAILABLE_ROLES
	CURRENT_SECONDARY_ROLES
	CURRENT_SESSION
	CURRENT_STATEMENT
	CURRENT_TRANSACTION
	CURRENT_USER
	GETVARIABLE
	LAST_QUERY_ID
	LAST_TRANSACTION
Session object context	CURRENT_DATABASE
	CURRENT_ROLE_TYPE
	CURRENT_SCHEMA
	CURRENT_SCHEMAS
	CURRENT_WAREHOUSE
	INVOKER_ROLE
	INVOKER_SHARE
	IS_DATABASE_ROLE_IN_SESSION
	IS_GRANTED_TO_INVOKER_ROLE
	IS_INSTANCE_ROLE_IN_SESSION
	IS_ROLE_IN_SESSION
	POLICY_CONTEXT
Alert context	GET_CONDITION_QUERY_UUID

Usage notes¶

Context functions generally do not require arguments.
To comply with the ANSI standard, the following context functions can be called without parentheses in SQL statements:
- CURRENT_DATE
- CURRENT_TIME
- CURRENT_TIMESTAMP
- CURRENT_USER
- LOCALTIME
- LOCALTIMESTAMP
Note

If you are setting a Snowflake Scripting variable to an expression that calls one of these functions (for example, my_var := <function_name>();), you must include the parentheses.

Examples¶

Display the current warehouse, database, and schema for the session:

SELECT CURRENT_WAREHOUSE(), CURRENT_DATABASE(), CURRENT_SCHEMA();

Copy

+---------------------+--------------------+------------------+
| CURRENT_WAREHOUSE() | CURRENT_DATABASE() | CURRENT_SCHEMA() |
|---------------------+--------------------+------------------+
| MY_WAREHOUSE        | MY_DB              | PUBLIC           |
|---------------------+--------------------+------------------+

Display the current date, time, and timestamp (note that parentheses are not required to call these functions):

SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP;

Copy

+--------------+--------------+-------------------------------+
| CURRENT_DATE | CURRENT_TIME | CURRENT_TIMESTAMP             |
|--------------+--------------+-------------------------------|
| 2024-06-07   | 10:45:15     | 2024-06-07 10:45:15.064 -0700 |
+--------------+--------------+-------------------------------+

In a Snowflake Scripting block, call the CURRENT_DATE function without parentheses to set a variable in a SQL statement:

EXECUTE IMMEDIATE
$$
DECLARE
  currdate DATE;
BEGIN
  SELECT CURRENT_DATE INTO currdate;
  RETURN currdate;
END;
$$
;

Copy

+-----------------+
| anonymous block |
|-----------------|
| 2024-06-07      |
+-----------------+

In a Snowflake Scripting block, attempting to set a variable to an expression that calls the CURRENT_DATE function without parentheses results in an error:

EXECUTE IMMEDIATE
$$
DECLARE
  today DATE;
BEGIN
  today := CURRENT_DATE;
  RETURN today;
END;
$$
;

Copy

000904 (42000): SQL compilation error: error line 5 at position 11
invalid identifier 'CURRENT_DATE'

The same block returns the current date when the function is called with the parentheses:

EXECUTE IMMEDIATE
$$
DECLARE
  today DATE;
BEGIN
  today := CURRENT_DATE();
  RETURN today;
END;
$$
;

Copy

+-----------------+
| anonymous block |
|-----------------|
| 2024-06-07      |
+-----------------+