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

Query

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');

Result

COLUMN1|COLUMN2|COLUMN3|COLUMN4|
-------+-------+-------+-------+
22     |22     |22     |22     |

Snowflake

Query

SELECT
    DAYOFMONTH(DATE'2022-12-22'),
    DAYOFMONTH(DATE'2022-12-22'),
    DAYOFMONTH(DATE'2022-12-22'),
    DAYOFMONTH(DATE'2022-12-22');

Result

COLUMN1|COLUMN2|COLUMN3|COLUMN4|
-------+-------+-------+-------+
22     |22     |22     |22     |

ISO calendar

Teradata

Query

SELECT DAYNUMBER_OF_MONTH (DATE'2022-12-22', 'ISO');

Result

COLUMN1|
-------+
25     |

Snowflake

Query

SELECT
PUBLIC.DAY_NUMBER_OF_MONTH_ISO_UDF(DATE'2022-12-22') /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'DAY_NUMBER_OF_MONTH_ISO_UDF' INSERTED. ***/;

Result

COLUMN1|
-------+
25     |

Known Issues

No known issues.

Related EWIs

1. MSCEWI1020: CUSTOM UDF 'DAY_NUMBER_OF_MONTH_ISO_UDF' INSERTED.