Search code examples
sql-serverssmscalculated-columns

Adding a calculated field to convert string value to date in T-SQL

I'm trying to add a calculated field to an existing table in SSMS, which will convert a string in the format YYYYMMDD to a date format, but I am getting errors regarding the string field not being valid.

I require the calculated field as I have '00000000' values (i.e. NULL) in the string field so can't use this in date calculations.

The code I'm using is :

ALTER TABLE [TEM].[AssignmentRates] 
    ADD [Date_Expired] DATE NULL
    (SELECT CONVERT([date], CASE WHEN [Expiry_Date]='00000000' THEN NULL ELSE [Expiry_Date] END))

where [Expiry_Date] is the string column I'm trying to convert, and [Date_Expired] is the name of the calculated column I'm trying to add.

I get this error:

Invalid column name 'Expiry_Date'

against both instances of that field name, and can't work out why. If I run the query as a stand alone SELECT it returns the required results.

Using table aliases or the full database, table and column name for it don't appear to work either.

It's probably something incredibly obvious, but I haven't been able to work out what it is.

Solution

  • The error on expiry_date seems quite clear -- that is not the name of a column in the table. But you can simplify the logic:

    ALTER TABLE TEM.AssignmentRates ADD Date_Expired AS
    (TRY_CONVERT(date, Expiry_Date));

    Actually, the nested SELECT may have caused an issue. That would not normally be used for a computed column.