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      |
+-----------------+