英文:
Trying to add a Date column to table, but getting MSG 156 Incorrect syntax near the keyword error
问题
我正在尝试创建一个根据开始日期和周计数列计算日期的列。以下是我的代码：
ALTER TABLE Salesforce_Expanded
ADD current_date DATE;
GO
UPDATE Salesforce_Expanded
SET current_date = DATEADD(week, Week_Count-1, start_date);
我遇到以下错误：
Msg 156, Level 15, State 1, Line 2
关键字 'current_date' 附近的语法不正确。Msg 156, Level 15, State 1, Line 6
关键字 'current_date' 附近的语法不正确。
英文:
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'.
答案1
得分: 2
我建议在这里使用一个计算列。
在新列中添加和更新值的问题在于，现在你有一个依赖于现有数据的值；可以独立更新任何其他依赖列，然后current_date立即成为无效或损坏的数据。
你可以通过触发器来强制维护该值，但更好的解决方案是实现一个计算列，它将始终是正确的。
Alter table Salesforce_Expanded
add [Current_Date] as DATEADD(week, Week_Count-1, start_date);
英文:
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);
答案2
得分: 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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论