0

I am trying to understand why my function is non-deterministic. I want to place this function on a computed column and Index on it. SQL complains that it is non deterministic.

ALTER FUNCTION [dbo].[GetPeriodFromDates]
(
    -- Add the parameters for the function here
    @from date,
    @to date
)
RETURNS char(1)
AS
BEGIN
    declare @result char(1)
    select @result = case 
        when DateAdd(day, -1, DateAdd(year, 1, @from)) = @to then 'Y'
        when DateAdd(day, -1, DateAdd(quarter, 1, @from)) = @to then 'Q'
        when DateAdd(day, -1, DateAdd(month, 1, @from)) = @to then 'M'
        when DateAdd(day, -1, DateAdd(week, 1, @from)) = @to then 'W'
        else NULL
    end

    return @result
END

The information on the web mentions casting and converting dates, but those examples don't seem to apply in my case. I understand how deterministic means that a function always returns the same output given the same input. I feel like this function does that.

I am confused as to what exactly the problem is.

jwrightmail
  • 907
  • 1
  • 13
  • 24
  • 1
    According to the documentation, this function should be deterministic, because it only calls `dateadd()`, which is deterministic (https://learn.microsoft.com/en-us/sql/relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions). – Gordon Linoff Feb 17 '18 at 00:03
  • 2
    Afaik a udf must use WITH SCEHMABINDING to be deterministic. – MatBailie Feb 17 '18 at 00:07
  • @MatBailie Thanks. I found this on line and it worked! https://stackoverflow.com/questions/3651662/sql-server-deterministic-user-defined-function – jwrightmail Feb 17 '18 at 00:13
  • This is the exact duplicate of the question you mentioned – Paul Karam Feb 17 '18 at 07:25

0 Answers0