9

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?

2
  • 3
    Call stack of error is i.stack.imgur.com/gBaGE.png. CQueryExecContext::FillExprCache is mentioned here sql.kiwi/2012/09/… - so it is trying to evaluate and cache the value at runtime rather than compile time but before the query execution "proper" begins. Maybe related to runtime constant nature of getdate()? Jan 10, 2022 at 23:50
  • It appears this is fixed in SQL Server 2019 CU16 and SQL Server 2022. Jan 12, 2023 at 23:22

2 Answers 2

5

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;
-2

in my case the time zone value was empty therefore, SQL was returning 9820 Error while getting UTC date/time value. Make sure in the above script @Z value ensure valid and not empty. for valid value click here.

1
  • This is not a general question about any random case where the 9820 error appears. It is asking about why it incorrectly appears in a specific case Jun 18, 2023 at 12:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.