TRUNC (date) UDF
Description
The
TRUNC
(date) function returnsdate
with the time portion of the day truncated to the unit specified by the format modelfmt
. (Oracle TRUNC(date) SQL Language Reference)
Oracle TRUNC
and Snowflake TRUNC
function with date arguments have some functional differences.
TRUNC_UDF
helper will be added to handle the following cases:
1. The format is not supported by Snowflake.
2. The format exists in Snowflake but works differently.
3. The tool cannot determine the datatype of the first argument.
4. The format is provided as a column or expression and not as a literal.
Custom UDF overloads
TRUNC_UDF(date)
It applies an explicit DATE
cast to the input Timestamp.
Parameters
INPUT: The Timestamp with Time Zone (TIMESTAMP_LTZ) that needs to be truncated.
The default parameter for the UDF is TIMESTAMP_LTZ
. It may need to be changed to TIMESTAMP_TZ
or TIMESTAMP_NTZ
to match the default TIMESTAMP
used by the user.
Oracle
Snowflake
TRUNC_UDF(date, fmt)
Manually creates a new date using DATE_FROM_PARTS()
function, depending on the format category used.
Parameters
DATE_TO_TRUNC: The Timestamp with Time Zone (TIMESTAMP_LTZ) that needs to be truncated.
DATE_FMT: The date format as a VARCHAR. Same formats that are supported in Oracle.
The default parameter for the UDF is TIMESTAMP_LTZ
. It may need to be changed to TIMESTAMP_TZ
or TIMESTAMP_NTZ
to match the default TIMESTAMP
used by the user.