DATEADD UDF

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Description

This UDF is used as a template for all cases when there is an addition between a DATE or TIMESTAMP type and FLOAT type.

Custom UDF overloads

DATEADD_UDF(date, float)

Parameters

1. FIRST_PARAM: The first DATE of the operation.

2. SECOND_PARAM: The FLOAT to be added.

UDF

CREATE OR REPLACE FUNCTION PUBLIC.DATEADD_UDF(FIRST_PARAM DATE, SECOND_PARAM FLOAT)
RETURNS DATE
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
SELECT FIRST_PARAM + SECOND_PARAM::NUMBER
$$;

DATEADD_UDF(float, date)

Parameters

1. FIRST_PARAM: The FLOAT to be added.

2. SECOND_PARAM: The DATE of the operation.

UDF

CREATE OR REPLACE FUNCTION PUBLIC.DATEADD_UDF(FIRST_PARAM FLOAT, SECOND_PARAM DATE)
RETURNS DATE
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
SELECT FIRST_PARAM::NUMBER + SECOND_PARAM
$$;

DATEADD_UDF(timestamp, float)

Parameters

1. FIRST_PARAM: The first TIMESTAMP of the operation.

2. SECOND_PARAM: The FLOAT to be added.

UDF

CREATE OR REPLACE FUNCTION PUBLIC.DATEADD_UDF(FIRST_PARAM TIMESTAMP, SECOND_PARAM FLOAT)
RETURNS TIMESTAMP
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
SELECT DATEADD(day, SECOND_PARAM,FIRST_PARAM)
$$;

DATEADD_UDF(float, timestamp)

Parameters

1. FIRST_PARAM: TheFLOAT of the operation.

2. SECOND_PARAM: TheTIMESTAMP of the operation.

UDF

CREATE OR REPLACE FUNCTION PUBLIC.DATEADD_UDF(FIRST_PARAM FLOAT, SECOND_PARAM TIMESTAMP)
RETURNS TIMESTAMP
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
SELECT DATEADD(day, FIRST_PARAM,SECOND_PARAM)
$$;

Usage example

Oracle

Query

IN -> Oracle_01.sql

SELECT
    TO_TIMESTAMP('03/08/2009, 12:47 AM', 'dd/mm/yy, hh:mi AM')+62.40750856543442
FROM DUAL;

Result

Snowflake

Query

OUT -> Oracle_01.sql

SELECT
    PUBLIC.DATEADD_UDF(TO_TIMESTAMP('03/08/2009, 12:47 AM', 'dd/mm/yy, hh:mi AM'), 62.40750856543442)
FROM DUAL;

Result

Known Issues

1. Differences in time precision

When there are operations between Dates or Timestamps and Floats, the time may differ from Oracle's. There is an action item to fix this issue.

Related EWIs

No EWIs related.