Click here to Skip to main content
15,867,936 members
Please Sign up or sign in to vote.
3.67/5 (2 votes)
Hi all,
I have a little problem in a SQL Server 2005 computed column.
I'm working with a legacy table which stores a date in a field defined as NULLABLE CHAR(10) in the format dd-MM-yyyy.
Of course that doesn't work at all... So I want to create a persisted computed column that stores the value as a smalldatetime.

First of all, the following query runs with no errors:
SQL
SELECT CONVERT(SMALLDATETIME, RIGHT(MyField, 4) + SUBSTRING(MyField, 4, 2) + LEFT(MyField, 2))
  FROM MyTable

When I put the above formula in the formula field in SQL Server it gives me the error "Error validating the formula for column 'MyComputedColumn'".
I can ignore the error and save my table and everything looks fine.

But now I want to make the computed column persisted and I get the following error:
"Unable to modify table.  
Computed column 'MyComputedColumn' in table 'Tmp_MyTable' cannot be persisted because the column is non-deterministic."

I've searched the problem on Google, but it all makes little sense to me...
Any idea's?
Thanks.
Posted
Updated 8-May-14 4:19am
v3

1 solution

It makes sense to me! :laugh:
This explains it: http://msdn.microsoft.com/en-us/library/ms178091.aspx[^]:
CONVERT
Deterministic unless one of these conditions exists:
Source type is sql_variant.
Target type is sql_variant and its source type is nondeterministic.
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.


So try adding a style greater than 100 to the end as the third CONVERT parameter.
 
Share this answer
 
Comments
Sander Rossel 8-May-14 10:40am    
That did the trick! I was first using a CAST, but I knew CONVERT should be the solution somehow.
I still get the error that there's an error validating my formula, but I'm ignoring it and everything is fine.
Thanks!
OriginalGriff 8-May-14 10:41am    
You're welcome!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900