[SOLVED] Trying to add a Date column to table, but getting MSG 156 Incorrect syntax near the keyword error

Trying to add a Date column to table, but getting MSG 156 Incorrect syntax near the keyword error

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'.

Solution

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);