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;