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.DATE_ADD_UDF(FIRST_PARAM DATE, SECOND_PARAM FLOAT)
RETURNS DATE
LANGUAGE SQL
IMMUTABLE
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.DATE_ADD_UDF(FIRST_PARAM FLOAT, SECOND_PARAM DATE)
RETURNS DATE
LANGUAGE SQL
IMMUTABLE
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.DATE_ADD_UDF(FIRST_PARAM FLOAT, SECOND_PARAM TIMESTAMP)
RETURNS TIMESTAMP
LANGUAGE SQL
IMMUTABLE
AS
$$
    SELECT DATEADD(day, FIRST_PARAM,SECOND_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.DATE_ADD_UDF(FIRST_PARAM FLOAT, SECOND_PARAM TIMESTAMP)
RETURNS TIMESTAMP
LANGUAGE SQL
IMMUTABLE
AS
$$
    SELECT DATEADD(day, FIRST_PARAM,SECOND_PARAM)
$$;

Usage example

Oracle

Query

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

SELECT
		--** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'DATE_ADD_UDF' INSERTED. **
		PUBLIC.DATE_ADD_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

1. MSCEWI1020: CUSTOM UDF INSERTED.