SnowflakeDocumentation Home

DATEDIFF UDF

Some parts in the output code are omitted for clarity reasons.

Description

This UDF is used as a template for all cases when there is a subtraction between a DATE, TIMESTAMP, and any other type (except Intervals).

Custom UDF overloads

DATEDIFF_UDF(date, date)

Parameters

  1. FIRST_PARAM: The first DATE of the operation.

  2. SECOND_PARAM: The DATE to be subtracted.

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

DATEDIFF_UDF(date, timestamp)

Parameters

  1. FIRST_PARAM: The first DATE of the operation.

  2. SECOND_PARAM: The TIMESTAMP to be subtracted.

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

DATEDIFF_UDF(date, integer)

Parameters

  1. FIRST_PARAM: The first DATE of the operation.

  2. SECOND_PARAM: The INTEGER to be subtracted.

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

DATEDIFF_UDF(timestamp, timestamp)

Parameters

  1. FIRST_PARAM: The first TIMESTAMP of the operation.

  2. SECOND_PARAM: The TIMESTAMP to be subtracted.

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

DATEDIFF_UDF(timestamp, date)

Parameters

  1. FIRST_PARAM: The first TIMESTAMP of the operation.

  2. SECOND_PARAM: The DATE to be subtracted.

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

DATEDIFF_UDF(timestamp, number)

Parameters

  1. FIRST_PARAM: The first TIMESTAMP of the operation.

  2. SECOND_PARAM: The NUMBER to be subtracted.

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

Usage example

The unknown is a column whose type could not be resolved, it could be a timestamp, date integer, or number.

--disableDateAsTimestamp

Flag to indicate whether SYSDATE should be transformed into CURRENT_DATE or CURRENT_TIMESTAMP. This will also affect all DATE columns that will be transformed to TIMESTAMP.

Oracle

IN -> Oracle_01.sql
--Create Table
CREATE TABLE times(AsTimeStamp TIMESTAMP, AsDate DATE);

--Subtraction operations
SELECT AsDate - unknown FROM times, unknown_table;
SELECT unknown - AsTimeStamp FROM times;
SELECT AsTimeStamp - unknown FROM times;
SELECT unknown - AsDate FROM times;

Snowflake

OUT -> Oracle_01.sql
--Create Table