Why does this TVF throw error 9820 with GETDATE() as an input parameter?

Question

I am testing on SQL Server 2019 CU14. Consider the following table-valued function created against a SQL Server database with compatibility level 130 or 140:

-- requires database compat 130 or 140 to see the issue
CREATE OR ALTER FUNCTION [dbo].[TVF_BUG_REPRO_2] (@O DateTime, @Z varchar(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT
CAST(
       CASE
              WHEN SZ.Zone1 > '' THEN (@O at time zone SZ.Zone1) at time zone 'Pacific Standard Time' 
              WHEN LEN(@Z) > 3 THEN (@O at time zone @Z) at time zone 'Pacific Standard Time'
              ELSE @O
       END AS DATETIME
) ConvertedDate
FROM (SELECT CASE @Z WHEN 'ET' THEN 'Eastern Standard Time' ELSE NULL END Zone1) SZ;

The following query executes without error and returns the expected results:

SELECT * FROM [dbo].[TVF_BUG_REPRO_2] ('2022-01-10 16:16:51.327', 'ET');

The following query unexpectedly throws an error:

SELECT * FROM [dbo].[TVF_BUG_REPRO_2] (GETDATE(), 'ET');

The error message is:

Msg 9820, Level 16, State 1, Line 27

The time zone parameter 'ET' provided to AT TIME ZONE clause is invalid.

I don't understand why I'm getting an error here. That code should never execute. Running the code without a function as a simple SELECT also does not throw any errors.

Why does that query fail? Could this be a bug in SQL Server?

score 5 · Answer 1 · edited Mar 14 '25 at 13:39

FWIW, a function of this form works:

CREATE OR ALTER FUNCTION [dbo].[TVF_BUG_REPRO_2] (@O DateTime, @Z varchar(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT
CAST(@O at time zone SZ.Zone at time zone 'Pacific Standard Time' AS DATETIME ) ConvertedDate
FROM (SELECT CASE @Z WHEN 'ET' THEN 'Eastern Standard Time' ELSE @Z END Zone) SZ;

It appears this is fixed in SQL Server 2019 CU16 and SQL Server 2022.

Why does this TVF throw error 9820 with GETDATE() as an input parameter?

1 Answers1