DATEADD UDF

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

|TO_TIMESTAMP('03/08/2009,12:47AM','DD/MM/YY,HH:MIAM')+62.40750856543442|
|-----------------------------------------------------------------------|
|2009-10-04 10:33:49.000                                                |

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

|PUBLIC.DATEADD_UDF(
TO_TIMESTAMP('03/08/2009, 12:47 AM', 'DD/MM/YY, HH12:MI AM'), 62.40750856543442)|
|-----------------------------------------------------------------------------------------------------|
|2009-10-04 00:47:00.000                                                                              |

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.