TRUNC Function - Oracle to Snowflake Migration

In Oracle, TRUNC(datetime, unit) function allows you to truncate a datetime value to the specified unit (set zero time, set the first day of the month i.e). The default unit is 'DD' (truncation to a day).

Snowflake also provides the TRUNC function, but it requires the unit to be specified (there is no default value) and unit values are different in Oracle and Snowflake.

Oracle:

  ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
 
  -- Get current datetime with the time set to zero (use the default unit 'DD')
  SELECT TRUNC(SYSDATE) FROM dual;
  # 2022-04-23 00:00:00
 
  -- Get current datetime with the time set to zero (set the unit 'DD' explicitly)
  SELECT TRUNC(SYSDATE, 'DD') FROM dual;
  # 2022-04-23 00:00:00

Snowflake:

  -- Get current datetime with the time set to zero
  SELECT TRUNC(CURRENT_TIMESTAMP(0), 'DAY');
  # 2022-04-23 00:00:00.000 +0000

Note that the unit values can be different in Oracle and Snowflake:

For more information, see Oracle to Snowflake Migration.