Sample Header Ad - 728x90

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

9 votes
1 answer
295 views
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?
Asked by Joe Obbish (32996 rep)
Jan 10, 2022, 10:59 PM
Last activity: Mar 14, 2025, 01:39 PM