How to resolve error with SQL Scalar variable using GETDATE

Question

I'm writing a scalar variable in SQL to define a season year (which starts halfway through a calendar year). It needs to change as the date changes i.e. 2021 for this date, on June 1 2022 then it should change to 2022.

It's generating this error code:

"Error referencing function 'GETDATE': non-deterministic, security and metadata function calls are not allowed in user-defined functions."

I can’t see any workarounds online that can deal with the issue in detail. Any ideas?

The code is:

CREATE FUNCTION CDP.fn_SeasonYear
(
)
RETURNS INT
       AS 
       BEGIN
              DECLARE @ThisSeason INT
       SET @ThisSeason = 
       CASE
              WHEN DatePart(Month, GETDATE()) < 6
              THEN CONVERT(int,DatePart(Year, GETDATE()) -1)
              ELSE CONVERT(int,DatePart(Year, GETDATE()))
       End

RETURN @ThisSeason
END;

You can't use GETDATE inside a user defined function. The reason is as the error says, its non deterministic meaning you will get different results with the same input. On thing that might work is to create a View thats filled with GETDATE and use the view in your function — garethb, Jul 14 '21 at 04:38
Keep in mind that UDF's are often not the right answer and can introduce performance issues. I would always go straight to using a calendar table for this kind of thing. — Nick.Mc, Jul 14 '21 at 05:14

score 1 · Answer 1 · answered Jul 14 '21 at 05:47

GETDATE() is MS SQL Server. You should tag the question as such. And you should probably indicate what version of SQL Server you have as the function works fine in SQL Server 2017:

SET NOCOUNT ON;
SELECT @@VERSION;
GO
DROP FUNCTION IF EXISTS dbo.fn_SeasonYear;
GO
CREATE FUNCTION dbo.fn_SeasonYear()
RETURNS INT
AS 
BEGIN
  DECLARE @ThisSeason INT;
  SET @ThisSeason = 
    CASE
      WHEN DatePart(Month, GETDATE()) < 6
        THEN CONVERT(INT, DatePart(Year, GETDATE()) -1)
      ELSE CONVERT(INT, DatePart(Year, GETDATE()))
    END;
RETURN @ThisSeason;
END;
GO
SELECT  dbo.fn_SeasonYear();

/*
OUTPUT:
----------------------------------------------------------------------------
Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) 
    Aug 22 2017 17:04:49 
    Copyright (C) 2017 Microsoft Corporation
    Express Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

-----------
2021
*/

+1 I was surprised by this. In Sql 2000 you definitely couldn't do this so I automatically avoided using getdate() in UDF's. Apparently they relaxed the rule in Sql 2005. I don't have Sql 2000 so I can't test how it behaves but from Sql 2005 onwards, in query/update statements, getdate() is evaluated once per column and that value is used for all rows. I suspect that getdate() is pseudo-deterministic, ie it gets called once in any execution context and the cached value is used. — John D, Jul 14 '21 at 06:37
@JohnD Yes, it is only evaluated once per context. What you really want to use is SYSUTCDATETIME( ). Like SYSDATE() it's evaluated every time, and you want to use it over SYSDATE() because you should NEVER store any timestamp values (datetime) in anything other than UTC (and your server should be in UTC...always. No exceptions). — quickdraw, Jul 25 '21 at 04:16

score 0 · Accepted Answer · answered Jul 14 '21 at 04:49

This is not ideal but it's an approach I've used. As mentioned, functions are required to return the same value given the same input. You pass getdate() as a parameter to the function.

CREATE FUNCTION dbo.fn_SeasonYear( @currdate datetime )

RETURNS INT
       AS 
       BEGIN
              DECLARE @ThisSeason INT
       SET @ThisSeason = 
       CASE
              WHEN DatePart(Month, @currdate) < 6
              THEN CONVERT(int,DatePart(Year, @currdate) -1)
              ELSE CONVERT(int,DatePart(Year, @currdate))
       End

RETURN @ThisSeason
END;

How to resolve error with SQL Scalar variable using GETDATE

2 Answers2