How to Get the Current Date and Time in Snowflake

Both are evaluated once per statement, so repeated calls within one query return the same value. They follow the TIMEZONE session parameter, which defaults to America/Los_Angeles on new accounts and catches out teams who assume UTC. ALTER SESSION SET TIMEZONE = 'UTC' fixes it for the connection.

SELECT CURRENT_DATE(), CURRENT_TIMESTAMP();

Yesterday, last 7 days, start of month

DATEADD accepts the unit quoted or unquoted and takes negative amounts for going backwards. DATE_TRUNC snaps to the start of the given unit, which is what month-to-date and week-to-date filters need. Comparing a timestamp column against DATEADD(day, -7, CURRENT_DATE()) keeps partition pruning intact, whereas wrapping the column in a function does not.

SELECT
  DATEADD(day, -1, CURRENT_DATE()) AS yesterday,
  DATEADD(day, -7, CURRENT_DATE()) AS week_ago,
  DATE_TRUNC('month', CURRENT_DATE()) AS month_start;

The session time zone decides what "today" is

CURRENT_DATE and CURRENT_TIMESTAMP are evaluated in the session TIMEZONE parameter, which on a new account defaults to America/Los_Angeles rather than UTC. That is why a scheduled job and an interactive query can disagree about the date for several hours a day. Set it explicitly on the user or the session, or use SYSDATE(), which always returns UTC.

SHOW PARAMETERS LIKE 'TIMEZONE';
ALTER SESSION SET TIMEZONE = 'UTC';

SELECT CURRENT_DATE(), CURRENT_TIMESTAMP(), SYSDATE();

TIMESTAMP_NTZ, LTZ and TZ behave differently

TIMESTAMP_NTZ stores wall-clock time with no zone, so it never shifts and never converts. TIMESTAMP_LTZ stores an instant and renders it in the reader's session zone, so the same row displays differently for two people. TIMESTAMP_TZ keeps the original offset alongside the instant. CURRENT_TIMESTAMP returns LTZ, which is why comparing it directly to an NTZ column gives results that move with whoever is running the query.

SELECT CURRENT_TIMESTAMP()::TIMESTAMP_NTZ AS fixed,
       CONVERT_TIMEZONE('UTC', CURRENT_TIMESTAMP()) AS in_utc;

One value per statement, not per row

CURRENT_TIMESTAMP is evaluated once for the whole statement, so every row a multi-row INSERT or UPDATE touches gets the identical value. That is usually what you want for a loaded_at column. Where it surprises people is inside a long-running multi-statement script, where each statement gets its own reading.

INSERT INTO orders (id, loaded_at)
SELECT id, CURRENT_TIMESTAMP() FROM staging_orders;