DateDiff() function reported as NonDeterministic

Question

Post reply

DateDiff() function reported as NonDeterministic

agiambalvo

SSC Veteran

Points: 288
More actions
February 26, 2014 at 3:32 pm

#288661

I am trying to assign the number of days from the beginning of a program to an event in that program as an indexed field in the ProgramDays table. The problem is that I cannot persist the column because SQL Server is reporting that the value of the DayNumber field is nondeterministic. The field is defined as:
[DayDate] [DATE] NOT NULL,
[DayNumber] AS ( DATEDIFF( DAY, '2014-05-01', [DayDate]) + (1) ) PERSISTED,
The exact error message is:
Computed column 'DayNumber' in table 'ProgramDays' cannot be persisted because the column is non-deterministic.
According to the documentation, the DATEDIFF() function is deterministic, so why is it telling me the column is non-deterministic?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

dogramone Hall of Fame Points: 3216 More actions · Answer 1

IS there any specific reason you are using "DAY" within the datediff or did you mean to use d or dd?

David Webb-CDS SSCoach Points: 17398 More actions · Answer 2

Don't have a place to test this right now but if setting DATEFIRST impacts the result, that could be why it might be considered non-deterministic.

And then again, I might be wrong ...
David Webb

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

You need to do an explicit CONVERT to use the string literal.

I changed your code a little bit to remove the +1

CREATE TABLE Test(

DayDate DATE,

DayNumber AS (DATEDIFF( DD, CONVERT( DATE, '2014-04-30', 120), DayDate)) PERSISTED)

INSERT INTO Test(DayDate) VALUES(GETDATE())

SELECT *

FROM Test

DROP TABLE Test

EDIT: Reference from Books OnLine

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.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286573 More actions · Answer 4

dogramone (2/26/2014)
IS there any specific reason you are using "DAY" within the datediff or did you mean to use d or dd?

Using the explicit value is generally considered better practice. That way you don't have reference BOL if you forget the shortcut.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

Sean Lange (2/27/2014)
dogramone (2/26/2014)
IS there any specific reason you are using "DAY" within the datediff or did you mean to use d or dd?
Using the explicit value is generally considered better practice. That way you don't have reference BOL if you forget the shortcut.

I guess that would be a matter of choice. I personally prefer the shorter 2 letter version in all cases. My feeling is that if someone doesn't know that "dd" stands for days or tha "mm" stands for months rather than minutes without having to make a trip to BOL, they probably shouldn't be working the code. 😀

The big reason for my preference is the damned colorizer in SQL Server makes the longer references turn pink because things like "DAY" are also functions. Many date calculations are complicated enough without having to decide if DAY is being used as a function or the first operand of a date function.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sean Lange SSC Guru Points: 286573 More actions · Answer 6

Jeff Moden (2/27/2014)
Sean Lange (2/27/2014)
dogramone (2/26/2014)
IS there any specific reason you are using "DAY" within the datediff or did you mean to use d or dd?
Using the explicit value is generally considered better practice. That way you don't have reference BOL if you forget the shortcut.
I guess that would be a matter of choice. I personally prefer the shorter 2 letter version in all cases. My feeling is that if someone doesn't know that "dd" stands for days or tha "mm" stands for months rather than minutes without having to make a trip to BOL, they probably shouldn't be working the code. 😀
The big reason for my preference is the damned colorizer in SQL Server makes the longer references turn pink because things like "DAY" are also functions. Many date calculations are complicated enough without having to decide if DAY is being used as a function or the first operand of a date function.

True enough. And for the "common" ones like DAY or MINUTE I frequently still use the shortcut. For the lesser used ones I tend to prefer the longer version so it removes any doubt that I really did mean WEEKDAY (dw) instead of d. 😀

By your standards I probably shouldn't be working the code because I frequently forget the DATEPART shortcuts. 😛

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 7

+1 @sean, I keep the important stuff in my brain but all the little odds-n-ends are cleaned out daily - that's why I have Google!!! 😀

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

agiambalvo SSC Veteran Points: 288 More actions · Answer 8

dogramone (2/26/2014)
IS there any specific reason you are using "DAY" within the datediff or did you mean to use d or dd?

I generally use the longer names for code readability. Also, in my code, a field or variable named DD (or @DD) would refer to the day of the month, which is not what I am looking for here.