[Update: This question describes a bug which has been fixed in Cumulative Update 5 for SQL Server 2019.]
Consider the following repro example (fiddle):
CREATE FUNCTION dbo.Repro (@myYear int)
RETURNS datetime
AS
BEGIN
IF @myYear <> 1990
BEGIN
RETURN NULL
END
DECLARE @firstOfYear datetime;
SET @firstOfYear = DATEFROMPARTS(@myYear, 1, 1);
IF DATEDIFF(day, @firstOfYear, @firstOfYear) <> 0
BEGIN
RETURN NULL
END
RETURN @firstOfYear
END
SELECT dbo.Repro(0);
Obviously, that function should return the first of January 1990 if the input is 1990
, and NULL
otherwise. Yes, I know that DATEDIFF(day, @firstOfYear, @firstOfYear) <> 0
is a nonsensical operation. This is a mcve to demonstrate a potential bug, not production code.
Now let's execute SELECT dbo.Repro(0)
on SQL Server 2017 and SQL Server 2019.
Expected result: NULL
.
Actual result on SQL Server 2017: NULL
Actual result on SQL Server 2019:
Msg 289 Level 16 State 1 Line 1
Cannot construct data type date, some of the arguments have values which are not valid.
Apparently, SQL Server 2019 executes some of the code below the initial guard clause (IF @myYear <> 1990
) even if it shouldn't.
My questions:
- Is this expected behavior, or did I find a bug in SQL Server 2019?
- If this is expected behavior, how do I correctly write a guard clause validating input paramters?