SnowflakeDocumentation Home

DATEDIFF UDF INTERVAL

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

Description

This UDF is used to resolve operations with intervals like:

  • INTERVAL - UNKNOWN

  • UNKNOWN - INTERVAL

  • DATE - INTERVAL

  • TIMESTAMP - INTERVAL

An UNKNOWN type is a column or expression whose type could not be resolved by Snow Convert, it use to happen when the DDLs for tables are not included in the migration or when there is an expression or subquery that can return different data types.

Custom UDF overloads

DATEADD_DDIF(string, date)

Parameters

  1. INTERVAL_VALUE: The interval String of the operation.

  2. D: The DATE where the interval will be subtracted.

CREATE OR REPLACE FUNCTION PUBLIC.DATEDIFF_UDF(INTERVAL_VALUE STRING,D DATE)
RETURNS DATE
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
WITH VARS(INPUT_VALUE, INPUT_PART) AS (
SELECT SUBSTR(INTERVAL_VALUE,11,POSITION('''',INTERVAL_VALUE,11)-11),
       TRIM(SUBSTR(INTERVAL_VALUE,POSITION('''',INTERVAL_VALUE,11)+1)))
SELECT 
    CASE WHEN INPUT_PART='YEAR(2) TO MONTH' OR INPUT_PART='YEAR(4) TO MONTH' THEN
        DATEADD(MONTHS,-1*PUBLIC.INTERVAL_TO_MONTHS_UDF(INPUT_VALUE),D) 
    WHEN INPUT_PART='MONTH' THEN
        DATEADD(MONTHS,-1*TO_NUMBER(INPUT_VALUE),D)    
    ELSE
        DATEADD(MICROSECONDS,-1*1000000*PUBLIC.INTERVAL_TO_SECONDS_UDF(INPUT_PART, INPUT_VALUE),D)::DATE
    END CASE    
FROM VARS    
$$;

DATEADD_DIFF(date, string)

Parameters

  1. D: The DATE where the interval will be subtracted.

  2. INTERVAL_VALUE: The interval String of the operation.

CREATE OR REPLACE FUNCTION PUBLIC.DATEDIFF_UDF(D DATE, INTERVAL_VALUE STRING)
RETURNS DATE
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
WITH VARS(INPUT_VALUE, INPUT_PART) AS (
SELECT SUBSTR(INTERVAL_VALUE,11,POSITION('''',INTERVAL_VALUE,11)-11),
       TRIM(SUBSTR(INTERVAL_VALUE,POSITION('''',INTERVAL_VALUE,11)+1)))
SELECT 
    CASE WHEN INPUT_PART='YEAR(2) TO MONTH' OR INPUT_PART='YEAR(4) TO MONTH' THEN
        DATEADD(MONTHS,-1*PUBLIC.INTERVAL_TO_MONTHS_UDF(INPUT_VALUE),D) 
    WHEN INPUT_PART='MONTH' THEN
        DATEADD(MONTHS,-1*TO_NUMBER(INPUT_VALUE),D)    
    ELSE
        DATEADD(MICROSECONDS,-1*1000000*PUBLIC.INTERVAL_TO_SECONDS_UDF(INPUT_PART, INPUT_VALUE),D)::DATE
    END CASE    
FROM VARS    
$$;

DATEADD_DIFF(string, timestamp)

Parameters

  1. INTERVAL_VALUE: The interval String of the operation.

  2. D: The TIMESTAMP where the interval will be subtracted.

CREATE OR REPLACE FUNCTION PUBLIC.DATEDIFF_UDF(INTERVAL_VALUE STRING,D TIMESTAMP)
RETURNS TIMESTAMP
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
WITH VARS(INPUT_VALUE, INPUT_PART) AS (
SELECT SUBSTR(INTERVAL_VALUE,11,POSITION('''',INTERVAL_VALUE,11)-11),
       TRIM(SUBSTR(INTERVAL_VALUE,POSITION('''',INTERVAL_VALUE,11)+1)))
SELECT 
    CASE WHEN INPUT_PART='YEAR(2) TO MONTH' OR INPUT_PART='YEAR(4) TO MONTH' THEN
        DATEADD(MONTHS,-1*PUBLIC.INTERVAL_TO_MONTHS_UDF(INPUT_VALUE),D) 
    WHEN INPUT_PART='MONTH' THEN
        DATEADD(MONTHS,-1*TO_NUMBER(INPUT_VALUE),D)    
    ELSE
        DATEADD(MICROSECONDS,-1*1000000*PUBLIC.INTERVAL_TO_SECONDS_UDF(INPUT_PART, INPUT_VALUE),D)
    END CASE    
FROM VARS    
$$;

DATEADD_DIFF(timestamp, string)

Parameters

  1. D: The TIMESTAMP where the interval will be subtracted.

  2. INTERVAL_VALUE: The interval String of the operation.

CREATE OR REPLACE FUNCTION PUBLIC.DATEDIFF_UDF(D TIMESTAMP, INTERVAL_VALUE STRING)
RETURNS TIMESTAMP
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
WITH VARS(INPUT_VALUE, INPUT_PART) AS (
SELECT SUBSTR(INTERVAL_VALUE,11,POSITION('''',INTERVAL_VALUE,11)-11),
       TRIM(SUBSTR(INTERVAL_VALUE,POSITION('''',INTERVAL_VALUE,11)+1)))
SELECT 
    CASE WHEN INPUT_PART='YEAR(2) TO MONTH' OR INPUT_PART='YEAR(4) TO MONTH' THEN
        DATEADD(MONTHS,-1*PUBLIC.INTERVAL_TO_MONTHS_UDF(INPUT_VALUE),D) 
    WHEN INPUT_PART='MONTH' THEN
        DATEADD(MONTHS,-1*TO_NUMBER(INPUT_VALUE),D)    
    ELSE
        DATEADD(MICROSECONDS,-1*1000000*PUBLIC.INTERVAL_TO_SECONDS_UDF(INPUT_PART, INPUT_VALUE),D)
    END CASE    
FROM VARS    
$$;

Usage example

--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