CAST_DATE 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
CAST_DATE_UDF(datestr)
It creates a DATE
from a STRING
.
Parameters
DATESTR: A
STRING
that represents aDATE
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
--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
--Create Table
CREATE OR REPLACE TABLE jsdateudf_table ( col1 TIMESTAMP
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
--Create Procedure
CREATE OR REPLACE PROCEDURE jsdateudf_proc (par1 TIMESTAMP)
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
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.
Last updated