Computed Column for AGE fails because it is non-deterministic
Posted on 2009-02-23
I am trying to create two computed columns in two tables which are, more or less, performing the same function and both failing for the same reason.
In one table I am trying to create a persisted computed AGE column from DOB, in the same table. I am using this as the formula which I took from another question off here:
(datediff(year,[dob],getdate())-case when CONVERT([char](5),[dob],(1))>CONVERT([char](5),getdate(),(1)) then (1) else (0) end)
When I go to exit SSMS and save the changes to the table an error occurs which reads "Computed column 'Age' in table 'Tmp_Respondents' cannot be persisted because the column is non-deterministic."
The other computed column in another table is to work out the number of years a person has been working by deducting "YearsofPractice" column from GETDATE. But the same problem occurs when I try to save the table.
There is no option to alter the column to be deterministic. I am new to MSSQL2008 and can't find anything about this is the books I am using. Any help would be appreciated.