Scalar function is non-deterministic-CodePudding

I'd like to persist this field but can't figure out why it's non-deterministic.

CREATE FUNCTION GetServiceMinutes
(
    -- Add the parameters for the function here
    @StartDate datetime,
    @EndDate datetime
)
RETURNS int WITH SCHEMABINDING
AS
BEGIN
    DECLARE @Result int

    SET @StartDate = CASE WHEN DATEPART(HOUR, @StartDate) < 8 THEN DATEADD(DAY, DATEDIFF(DAY, 0, @StartDate), '08:00:00')  ELSE @StartDate END
    SET @EndDate = CASE WHEN DATEPART(HOUR, @EndDate) >= 17 THEN DATEADD(DAY, DATEDIFF(DAY, 0, @EndDate), '17:00:00') ELSE @EndDate END
    
    SET @Result = 
         CASE WHEN DATEPART(DAY, @StartDate) != DATEPART(DAY, @EndDate) THEN 1000
              WHEN DATEPART(HOUR, @EndDate) < 8 THEN 0
              WHEN DATEPART(HOUR, @StartDate) >= 17 THEN 0
              ELSE DATEDIFF(MINUTE, @StartDate, @EndDate)
    END
    
    RETURN @Result

END
GO

This returns 0

SELECT OBJECTPROPERTY(OBJECT_ID('[dbo].GetServiceMinutes'), 'IsDeterministic')

Using SQL Server 2017

CodePudding user response：

The problem, as I alluded to, is your implicit conversions with (date and) time values. Per the documentation:

The following functions are not always deterministic, but can be used in indexed views or indexes on computed columns when they are specified in a deterministic manner.

Function Comments

all aggregate functions All aggregate functions are deterministic unless they are specified with the OVER and ORDER BY clauses. For a list of these functions, see Aggregate Functions (Transact-SQL).

CAST Deterministic unless used with datetime, smalldatetime, or sql_variant.

CONVERT Deterministic unless one of these conditions exists:
Source type is sql_variant.
Target type is sql_variant and its source type is nondeterministic.
Source or target type is datetime or smalldatetime, the other source or target type is a character string, and a nondeterministic style is specified. To be deterministic, the style parameter must be a constant. Additionally, styles less than or equal to 100 are nondeterministic, except for styles 20 and 21. Styles greater than 100 are deterministic, except for styles 106, 107, 109 and 113.

Function	Comments
all aggregate functions	All aggregate functions are deterministic unless they are specified with the OVER and ORDER BY clauses. For a list of these functions, see Aggregate Functions (Transact-SQL).
CAST	Deterministic unless used with datetime, smalldatetime, or sql_variant.
CONVERT	Deterministic unless one of these conditions exists: Source type is sql_variant. Target type is sql_variant and its source type is nondeterministic. Source or target type is datetime or smalldatetime, the other source or target type is a character string, and a nondeterministic style is specified. To be deterministic, the style parameter must be a constant. Additionally, styles less than or equal to 100 are nondeterministic, except for styles 20 and 21. Styles greater than 100 are deterministic, except for styles 106, 107, 109 and 113.

In your function you have implicit casting, for example '08:00:00' to a datetime. You need to be explicit:

CREATE FUNCTION dbo.GetServiceMinutes
(
    -- Add the parameters for the function here
    @StartDate datetime,
    @EndDate datetime
)
RETURNS int WITH SCHEMABINDING
AS
BEGIN
    DECLARE @Result int

    SET @StartDate = CASE WHEN DATEPART(HOUR, @StartDate) < 8 THEN DATEADD(DAY, DATEDIFF(DAY, 0, @StartDate), CONVERT(datetime,'1900-01-01T08:00:00',126))  ELSE @StartDate END
    SET @EndDate = CASE WHEN DATEPART(HOUR, @EndDate) >= 17 THEN DATEADD(DAY, DATEDIFF(DAY, 0, @EndDate), CONVERT(datetime,'1900-01-01T17:00:00',126)) ELSE @EndDate END
    
    SET @Result = 
         CASE WHEN DATEPART(DAY, @StartDate) != DATEPART(DAY, @EndDate) THEN 1000
              WHEN DATEPART(HOUR, @EndDate) < 8 THEN 0
              WHEN DATEPART(HOUR, @StartDate) >= 17 THEN 0
              ELSE DATEDIFF(MINUTE, @StartDate, @EndDate)
    END
    
    RETURN @Result

END
GO

SELECT OBJECTPROPERTY(OBJECT_ID('[dbo].GetServiceMinutes'), 'IsDeterministic')