-2

I'm attempting to create a column which calculates the date based on the start date and a week count column. Below is my code:

ALTER TABLE Salesforce_Expanded
    ADD current_date DATE;
GO

UPDATE Salesforce_Expanded
SET current_date = DATEADD(week, Week_Count-1, start_date);

I'm getting the following errors:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'current_date'.

Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'current_date'.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user21126867
  • 47
  • 8
  • 4
    [Reserved Keywords (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/reserved-keywords-transact-sql?view=sql-server-ver16). – Ponder Stibbons May 17 '23 at 16:03
  • 2
    Try using`[current_date]` or `CurrentDate`; Alternatively consider _not_ storing data that can be derived from an existing column, use a *computed column* – Stu May 17 '23 at 16:06
  • oh wow... I feel so dumb! Thanks! I Just renamed the column and it worked! – user21126867 May 17 '23 at 16:06
  • 1
    this will work: ALTER TABLE Salesforce_Expanded ADD [current_date] Date null; – Power Mouse May 17 '23 at 16:11

2 Answers2

2

I would recommend using a computed column here.

The problem with adding and updating values in a new column is that you now have a value that is dependent on existing data; it is possible to update any of the other dependent columns in isolation and then current_date is immediately invalid or corrupted data.

You could enforce maintaining the value via a trigger, however a better solution would be to implement a computed column, which will always be correct.

Alter table Salesforce_Expanded
add [Current_Date] as DATEADD(week, Week_Count-1, start_date);
Stu
  • 30,392
  • 6
  • 14
  • 33
0

As mentioned in the comments, I was using a Reserved Keyword as the name of the column. Changing the name of the column fixed the issue.

user21126867
  • 47
  • 8