CAST_DATE UDF

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

Description

This custom UDF is added to avoid runtime exceptions caused by format differences when casting strings to DATE, inside procedures and functions.

Custom UDF overloads

CAST_DATE_UDF(datestr)

It creates a DATE from a STRING.

Parameters

  1. DATESTR: A STRING that represents a DATE with a specific format.

CREATE OR REPLACE FUNCTION PUBLIC.CAST_DATE_UDF(DATESTR STRING) 
RETURNS DATE
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
	SELECT TO_DATE(DATESTR,'YYYY-MM-DD"T"HH24:MI:SS.FF')
$$;

Oracle

IN -> Oracle_01.sql
--Create Table
CREATE TABLE jsdateudf_table( col1 DATE );

--Create Procedure
CREATE OR REPLACE PROCEDURE jsdateudf_proc ( par1 DATE )
IS
BEGIN
    INSERT INTO jsdateudf_table VALUES(par1);
END;

--Insert Date
CALL jsdateudf_proc('20-03-1996');

--Select
SELECT * FROM jsdateudf_table;

Snowflake

OUT -> Oracle_01.sql
--Create Table
CREATE OR REPLACE TABLE jsdateudf_table ( col1 TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;

--Create Procedure
CREATE OR REPLACE PROCEDURE jsdateudf_proc (par1 TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        INSERT INTO jsdateudf_table
        VALUES(:par1);
    END;
$$;

--Insert Date
CALL jsdateudf_proc('20-03-1996');

--Select
SELECT * FROM
    jsdateudf_table;

Known Issues

1. Oracle DATE contains TIMESTAMP

  1. SSC-FDM-OR0042: Date Type Transformed To Timestamp Has A Different Behavior

Last updated