Add month column in 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：

you only need add a int column, you also can add check rule on this column, For example:

ALTER TABLE dbo.MyTable ADD mn INT CHECK(mn BETWEEN 1 AND 12)

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.