JSDATE UDF

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

JS_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.JS_DATE_UDF(DATESTR STRING) 
RETURNS DATE LANGUAGE SQL IMMUTABLE AS
$$
	SELECT TO_DATE(DATESTR,'YYYY-MM-DD"T"HH24:MI:SS.FF')
$$;

Oracle

Query

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

--Create Table
CREATE OR REPLACE TABLE PUBLIC.jsdateudf_table ( col1 DATE);

--Create Procedure
CREATE OR REPLACE PROCEDURE PUBLIC.jsdateudf_proc (par1 DATE )
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   // END REGION

   EXEC(`INSERT INTO PUBLIC.jsdateudf_table VALUES(/*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'JSDATE_UDF' INSERTED. ***/
PUBLIC.JSDATE_UDF(?))`,[PAR1]);
$$;

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

--Select
SELECT * FROM PUBLIC.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. MSCEWI1020: CUSTOM UDF INSERTED.