"Error validating formula" when adding Computed Column with CONVERT(DATE, column)

calculated-columns, sql, sql-server, ssms

Solution

Management Studio is not the best way to perform most DDL. Please open a new query window and use:

ALTER TABLE dbo.whatever ADD newcolumn AS (CONVERT(DATE, CharDob));

Here is the specific bug:

http://connect.microsoft.com/SQLServer/feedback/details/291134/katmai-table-designer-cant-validate-computed-column-formula

"Next release" in this context is most certainly SQL Server 2012, so if you are using 2008 or 2008 R2 SSMS (why?), the fix won't apply to you.

Problem

I am trying to add a computed column to a table via SSMS - I'd rather care not to get into the details on why the base column is a `VARCHAR(10)` and not a `DATE` and no, I'm not interested in changing it. When I enter the formula `CONVERT(DATE, [charDob])` in the table designer SSMS reports: Error validating the the formula for column `charDobTyped`. Using the formula `[charDob]` "works" as expected, meaning the column is bound correctly. Also the formula `CONVERT(DATE, '2001')` "works" as expected - so there is something with using the column that is causing the error. The original column is a `VARCHAR(10)` and contains NULL values or values that are otherwise convertible to `DATE`. The query `SELECT CONVERT(DATE,charDob) as dob FROM people` works as expected. Also, the error message is about "invalid formula" and not about a data conversion error, so I don't believe that inconvertible is causing this. I have already saved the table with the new computed column and a dummy value and reconnected to the database - this was to ensure that there was not an odd caching issue occurring, as sometimes occurs. The error persists independent of the persisted flag - if only it were that easy to turn off.

Original source