MSCEWI4054
Explicit cast to DATE added to return value.
Low
This EWI is added when an input user defined function has DATE as its returning type. Its main purpose is informative.
In SQL Server it is possible to return a DATETIME value in a DATE-returning UDF. This is because SQL Server performs an implicit cast. In Snowflake, this is not the case, and compilation time errors may appear if a mismatch exists between the result type and the type stated by the UDF as its return type.
To avoid this, we add a
:: DATE cast to the result of the UDF, inside the outer SELECT statement of the output CTE. This way we ensure congruency between the stated return type and the actual returned value.CREATE FUNCTION df.CleanDate ( @pdate date )
returns date
as
begin
declare @rdate date
SELECT @rdate =
CASE
WHEN isnull(nullif(@pdate, '01/01/1900'), '12/31/1899') = '12/31/1899' THEN convert(datetime, '12/31/1899')
WHEN convert(date, @pdate) <= '01/01/1990' THEN convert(date, '12/30/1899')
WHEN convert(date, @pdate) > '12/31/2024' THEN convert(date, '12/30/1899')
ELSE convert(date, @pdate)
END
return @rdate
end
/*** MSC-WARNING - MSCEWI1069 - USER DEFINED FUNCTION INNER LOGIC WAS TRANSFORMED TO A SINGLE COMMON TABLE EXPRESSION ***/
/*** MSC-WARNING - MSCEWI4054 - EXPLICIT CAST TO DATE ADDED TO RETURN VALUE TO MAINTAIN TYPE EQUIVALENCE ***/
CREATE OR REPLACE FUNCTION dwmeta.fnCleanDate (PDATE DATE)
returns DATE
LANGUAGE SQL
AS
$$
WITH CTE1 AS (
SELECT
CASE
WHEN NVL(NULLIF(PDATE, '01/01/1900'), '12/31/1899') = '12/31/1899' THEN CAST('12/31/1899' AS DATETIME)
WHEN CAST(PDATE AS DATE) <= '01/01/1990' THEN CAST('12/30/1899' AS DATE)
WHEN CAST(PDATE AS DATE) > '12/31/2024' THEN CAST('12/30/1899' AS DATE)
ELSE CAST(PDATE AS DATE)
END AS RDATE
)
SELECT
RDATE :: DATE
FROM
CTE1
$$;
- For more support, you can email us at [email protected] or post a message to our forums. If you have a contract for support with Mobilize.Net, reach out to your sales engineer and they can direct your support needs.
Last modified 4mo ago