CODEWITHSUNDEEP
sql-serverssmscalculated-columns
chatters
chatters

Reputation: 11

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.

Upvotes: 0

Views: 1390

Answers (3)

shawnt00
shawnt00

Reputation: 17915

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.

Upvotes: 0

user2577477
user2577477

Reputation: 1

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
)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

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.

Upvotes: 2

Related Questions