The DATEPART function returns an integer value for the given date part. The date part could be the year, quarter, month, week, day etc.,
A deterministic function should return the same result for the specific input at any circumstance.
The following code considered to be deterministic because when it executed over the time it always returns the same output.
SELECT DATEPART(QUARTER, '20110622')
Whereas weekday or week part is not guaranteed to return the same result hence it is treated as non-deterministic.
Let’s look at an example why weekday or week is non-deterministic
SELECT @@DATEFIRST DF, DATEPART (WEEKDAY, '20110622') WD -- 2011-06-22 is Wednesday
The query returns 7 as date first and 4 for the weekday. The weekday for the given input is 4 because the default first day of the week is 7 which is Sunday.
SET DATEFIRST 1 SELECT @@DATEFIRST DF, DATEPART (WEEKDAY, '20110622') WD -- 2011-06-22 is Wednesday
The weekday returns 3 for the above query as the date first value set explicitly as 1 (Monday)
The result of the DATEPART function for weekday or week is controlled by the DATEFIRST session setting. It is not guaranteed to return the same result for specific input hence it is treated as a non-deterministic function. As it is non-deterministic it cannot be used as PERSISTED column.