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

Question

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.

The error seems pretty clear. You need to reference a valid column. — Gordon Linoff, Oct 01 '19 at 18:05

score 2 · Accepted Answer · answered Oct 01 '19 at 18:07

2

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.

answered Oct 01 '19 at 18:07

Gordon Linoff

1,242,037
58
646
786

The TRY_CONVERT worked for me in this scenario. Thanks for your help Gordon. Much appreciated. – chatters Oct 02 '19 at 11:14

score 0 · Answer 2 · edited Oct 01 '19 at 19:12

0

Looks like a syntax issue

alter table [TEM].[AssignmentRates] 
    ADD [Date_Expired]
as 
(
case Expiry_Date when '00000000' then null else cast(Expiry_Date as date) end
)

edited Oct 01 '19 at 19:12

Dale K

25,246
15
42
71

answered Oct 01 '19 at 18:47

user2577477

1
1

score 0 · Answer 3 · answered Oct 01 '19 at 21:07

Your syntax was invalid for a computed column. What you had was actually adding a regular column (successfully) but then the attempting to run a select statement which was causing those column-name errors as it didn't have a from clause for context.

ALTER TABLE TEM.AssignmentRates /* don't do this */
ADD Date_Expired DATE NULL /* implied end of statement */
(SELECT CONVERT(date, CASE WHEN Expiry_Date = '00000000' THEN NULL ELSE Expiry_Date END));

Had you used the proper syntax, you'd at least get an informative error:

ALTER TABLE TEM.AssignmentRates /* error! */
ADD Date_Expired AS
  (SELECT CONVERT(date, CASE WHEN Expiry_Date = '00000000' THEN NULL ELSE Expiry_Date END);

/*ERROR: Subqueries are not allowed in this context. Only scalar expressions are allowed.*/

But really you didn't need a subquery in the first place:

ALTER TABLE TEM.AssignmentRates /* success */
ADD Date_Expired AS
  CONVERT(date, CASE WHEN Expiry_Date = '00000000' THEN NULL ELSE Expiry_Date END);

Gordon has a point about just using try_convert() though.

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

3 Answers3