MSCEWI4054

Explicit cast to DATE added to return value.

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Low

Description

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.

Code Example

Input Code:

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

Output Code:

/*** 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
$$;

Recommendations

• If you need more support, you can email us at [email protected]