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

Oracle and Snowflake TRUNC Units

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

Truncation Oracle Units Snowflake Units Truncated Example
Day 'DD', 'DDD' 'DAY', 'DAYS', 'D', 'DD', 'DAYOFMONTH' 2022-04-23 00:00:00
Starting day of the week 'DAY' , 'DY', 'D' 'WEEK', 'WK', 'W' 2022-04-17 00:00:00 (Sunday)

For more information, see Oracle to Snowflake Migration.