Computed Column 'month' In Table Cannot Be Persisted Because The Column Is Non-deterministic

I am getting one error when I try to make my field IsPersisted=True This is the definition of my table CREATE TABLE [dbo].[AdvanceMST]( [AdvanceID] [bigint] IDENTITY(1,1) NOT NULL,

Solution 1:

The datename function can return different results dependant on the language of the logged in user hence is not deterministic. Marking a computed column as persisted means SQL Server stores the results of the computation which requires there to be exactly one result.

If you need this as persisted at all you can replace with a 12 branch case expression with the specific language you want used.

Although it would probably be all round better to have month(AppliedDate) as a persisted integer column and have the datename function in a non persisted computed column.

Edited to give example as per comments

CREATETABLE #T
  (
     [AppliedDate] DATETIME,
     [Month] ASCASEMONTH([AppliedDate])
          WHEN1THEN'January'WHEN2THEN'February'WHEN3THEN'March'WHEN4THEN'April'WHEN5THEN'May'WHEN6THEN'June'WHEN7THEN'July'WHEN8THEN'August'WHEN9THEN'September'WHEN10THEN'October'WHEN11THEN'November'WHEN12THEN'December'END PERSISTED
  )  

  INSERTINTO #T([AppliedDate]) VALUES (GETDATE())