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.