I want to select all columns from a table into a #temp1 table based on criteria. In addition, I want to add an additional column that is the year portion only of a captured date field. I want the original whole date and add a separate column of just the year part of each date.

I tried to alter the #temp1 table and add a column called EnteredYear. I then tried an update with a set EnteredYear = DATEPART(year, EnteredDate), but I am getting a syntax error.

Searching docs.microsoft.com and StackOverflow, but I haven't hit upon the right syntax yet.

CodePudding user response：

If I understand correctly, you can try to use the computed column which needs to ALTER TABLE because it is a schema change.

ALTER TABLE #Temp ADD EnteredYear AS DATEPART(year, EnteredDate)

sqlfiddle

CodePudding user response：

Temp tables work almost like persisten tables. You manipulate them wit INSERT, UPDATE DELETE statements. So if you have already added your new column you could write:

UPDATE #temp1 set EnteredYear = DATEPART(year, EnteredDate)