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.

UDF

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

Query

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;

Result

COL1                   |
-----------------------+
1996-03-20 00:00:00.000|

Snowflake

Query

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;

Result

COL1      |
----------+
1996-03-20|

Known Issues

1. Oracle DATE contains TIMESTAMP

Take into consideration that Oracle DATE contains an empty TIMESTAMP (00:00:00.000), while Snowflake DATE does not. SnowConvert allows transforming DATE to TIMESTAMP with the SysdateAsCurrentTimestamp flag.

Related EWIs

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