DAYNUMBER_OF_MONTH

Translation specification for the transformation of the Teradata DAYNUMBER_OF_MONTH function to its Snowflake equivalent

Description

Returns the number of days elapsed from the beginning of the month to the given date. For more information check DAYNUMBER_OF_MONTH.

DAYNUMBER_OF_MONTH(expression [, calendar_name])

Both Teradata and Snowflake handle the DAYNUMBER_OF_MONTH function in the same way, except in one case:

  • The ISO calendar: An ISO month has 4 or 5 complete weeks. For more information check About ISO Computation.

To ensure functional equivalence, a user-defined function (UDF) is added for the ISO calendar case.

Sample Source Patterns

Teradata

IN -> Teradata_01.sql
SELECT
    DAYNUMBER_OF_MONTH (DATE'2022-12-22'),
    DAYNUMBER_OF_MONTH (DATE'2022-12-22', NULL),
    DAYNUMBER_OF_MONTH (DATE'2022-12-22', 'Teradata'),
    DAYNUMBER_OF_MONTH (DATE'2022-12-22', 'COMPATIBLE');

Snowflake

OUT -> Teradata_01.sql
SELECT
    DAYOFMONTH(DATE'2022-12-22'),
    DAYOFMONTH(DATE'2022-12-22'),
    DAYOFMONTH(DATE'2022-12-22'),
    DAYOFMONTH(DATE'2022-12-22');

ISO calendar

Teradata

IN -> Teradata_02.sql
SELECT DAYNUMBER_OF_MONTH (DATE'2022-12-22', 'ISO');

Snowflake

OUT -> Teradata_02.sql
SELECT
PUBLIC.DAYNUMBER_OF_MONTH_UDF(DATE'2022-12-22');

Known Issues

No issues were found.

No related EWIs.

Last updated