0

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

Cooper
  • 189
  • 1
  • 12
  • Implicit conversion of your (date and) time strings is a very likely candidate. That isn't allowed. It must be *explicit* with a style code. – Thom A Dec 14 '21 at 14:28
  • I'm also confused by that expression. You are adding *days* to `'08:00:00'`, but that is clearly a `time`, not a date and time. – Thom A Dec 14 '21 at 14:29

1 Answers1

3

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.

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')
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • That did the trick thanks! The expression to get 8am for the date was something I found on here, it worked so I didn't give it much thought. – Cooper Dec 14 '21 at 14:46