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
Last activity: Mar 14, 2025, 01:39 PM