5

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.

  • Have you tried using a proper `ALTER TABLE` statement instead of the UI which is riddled with all kinds of bugs? Or maybe you need to clarify what you mean by `try to *use* the formula`? – Aaron Bertrand Mar 29 '13 at 21:41
  • 1
    @AaronBertrand Ding ding ding. Point goes to you - yes, it does work correctly when doing it via DROP/ADD. (Silly me, I should have tried this sooner.) Post that as an answer for an accept/vote. –  Mar 29 '13 at 21:44

1 Answers1

9

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.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • On a side note, if one wants the date conversion to be deterministic, the style must be applied (e.g. `CONVERT(DATE,x,20)`) .. –  Mar 29 '13 at 21:54
  • @pst yes persistence has some additional requirements particularly with date/time types. Anyway glad the fix is simple for you. Suggest using the newer version of SSMS - lots of these little bugs are fixed and there are also a lot of improved features. – Aaron Bertrand Mar 29 '13 at 21:58
  • 3
    SQL Server 2016 (SSMS 13.0.16106.4 against SQL 12.0.4100.1). This is still a bug. An annoying one. I can't rearrange my columns because SSMS says the formula can't be validated even though I added them with T-SQL – Nick.Mc Mar 28 '17 at 00:24