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?
Answer :
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;