CODEWITHSUNDEEP
sqlsql-servertemp-tables
shykitten
shykitten

Reputation: 35

Can I add a column to a temporary table that stores part of a date (year) from another column?

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 learn.microsoft.com and StackOverflow, but I haven't hit upon the right syntax yet.

Upvotes: 0

Views: 896

Answers (2)

Johannes Krackowizer
Johannes Krackowizer

Reputation: 640

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)

Upvotes: 0

D-Shih
D-Shih

Reputation: 46219

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

Upvotes: 1

Related Questions