How to change data type from date to int in SQL Server 2012?
I have a table like
CREATE TABLE Student
(
s_id int NOT NULL IDENTITY(1,1),
sname nvarchar(30) NOT NULL,
address nvarchar(30) NOT NULL,
gender varchar(10) NOT NULL,
birthyear date NOT NULL,
CONSTRAINT PK_Student PRIMARY KEY (s_id)
);
Now I want to change the data type of the column birthyear
from date
to integer
.
I followed the w3school.com tutorial:
ALTER TABLE table_name
ALTER COLUMN column_name datatype
Here is my code:
ALTER TABLE Student
ALTER COLUMN birthyear int
But this throws an error
Operand type collision: date is incompatible with int
Can you help me? You are welcome.
Thank!
source to share
You cannot do it directly - a is DATE
not INT
- how is SQL Server supposed to convert a date like this 2015-05-07
to INT
??
You basically have two options:
Option # 1 : Rename the current column birthyear
to birthdate
and add a calculated column birthyear
that will give you the year of that date only:
-- rename "birthyear" to "birthdate"
EXEC sp_RENAME 'Student.Birthyear' , 'BirthDate', 'COLUMN'
-- add new computed column "birthyear"
ALTER TABLE dbo.Student
ADD BirthYear AS YEAR(birthdate) PERSISTED
Option # 2 : create a new column, put the year of your date in this column, delete the old column, rename the new column to the old name
-- add new column "newbirthyear"
ALTER TABLE dbo.Student
ADD NewBirthYear INT
-- update table, extract YEAR(birthyear) into new column
UPDATE dbo.Student
SET NewBirthYear = YEAR(birthyear)
-- drop old column
ALTER TABLE dbo.Student
DROP COLUMN birthyear
-- rename new column back to old column name
EXEC sp_RENAME 'Student.NewBirthyear' , 'BirthYear', 'COLUMN'
source to share
When the column is empty (all NULL), you can take the transition step using varchar conversion. Direct conversion is not possible, but this should work:
date -> varchar -> int
ALTER TABLE Student ALTER COLUMN birthyear varchar(200);
ALTER TABLE Student ALTER COLUMN birthyear int;
See this answer .
source to share