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

sqlsql-servertemp-tables


Solution 1:[1]

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

Solution 2:[2]

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)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 D-Shih
Solution 2 Johannes Krackowizer

Related Questions