add month column sql server-CodePudding

How to add a column month : the month must be extracted from a date column in the same table . I know that this extracts the month but how to add it please

select MONTH(columndate) as month
FROM TableTIME;

CodePudding user response：

DROP TABLE IF EXISTS dbo.Temp;
CREATE TABLE dbo.Temp
(
   Temp_ID TINYINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
   TempDate DATE NOT NULL DEFAULT GETDATE()
)
GO

INSERT dbo.Temp(TempDate)
   VALUES(GETDATE()),('20120430');
 GO

ALTER TABLE dbo.Temp
ADD  TempMonth AS DATEPART(MM,Tempdate) PERSISTED
GO

SELECT * FROM dbo.Temp;

CodePudding user response：

If you really need to have the month column, just add an integer column to the table and do and update with the extracted month:

update TableTIME set monthcolumn = MONTH(columndate)

It's not clear what is the purpose of that but, if this month will never change, I suggest you to select the MONTH(columndate) only when you need it or the full date and extract the month in the business logic.