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



  • 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?



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



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2