CODEWITHSUNDEEP
sql-server
eleethesontai
eleethesontai

Reputation: 556

Non Deterministic Function

sql server is telling me that this function is non deterministic. I am confused because given date x it will always return the same.

DATEADD(d, 13 - DATEDIFF(d, '9/23/10', serviceDate) % 14, serviceDate)

Upvotes: 0

Views: 310

Answers (1)

Horaciux
Horaciux

Reputation: 6477

  CONVERT(DATE,  DATEADD(d, 13 - DATEDIFF(d, CONVERT(DATE, '9/23/10',101), serviceDate) % 14, serviceDate),101)

When you refer to date data type string literals in indexed computed columns in SQL Server, we recommend that you explicitly convert the literal to the date type that you want by using a deterministic date format style. For a list of the date format styles that are deterministic, see CAST and CONVERT. Expressions that involve implicit conversion of character strings to date data types are considered nondeterministic, unless the database compatibility level is set to 80 or earlier. This is because the results depend on the LANGUAGE and DATEFORMAT settings of the server session. For example, the results of the expression CONVERT (datetime, '30 listopad 1996', 113) depend on the LANGUAGE setting because the string '30 listopad 1996' means different months in different languages. Similarly, in the expression DATEADD(mm,3,'2000-12-01'), the Database Engine interprets the string '2000-12-01' based on the DATEFORMAT setting.

CAST

Deterministic unless used with datetime, smalldatetime, or sql_variant.

CONVERT

Deterministic unless one of these conditions exists:

...

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.

Upvotes: 1

Related Questions