CODEWITHSUNDEEP
sql-serverdatabase
Loint
Loint

Reputation: 3938

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 data type of column birthyear from date to integer.

I followed tutorial of w3school.com :

ALTER TABLE table_name
ALTER COLUMN column_name datatype

Here is my code:

ALTER TABLE Student 
ALTER COLUMN birthyear int

But it throws an error

Operand type clash: date is incompatible with int

Can you people help me? Please.

Thank you!

Upvotes: 8

Views: 19155

Answers (2)

wladimirec
wladimirec

Reputation: 387

When column is empty (all NULLs) you can make transitional step by 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.

Upvotes: 18

marc_s
marc_s

Reputation: 754428

You can't do this directly - a DATE isn't an INT - how should SQL Server convert a date like 2015-05-07 into an INT ??

You have basically two options:

Option #1: rename the current column birthyear to birthdate and add a computed column birthyear that gives you the year only of that date:

-- 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 new column, put the year of your date into that column, drop old column, rename new column to 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'

Upvotes: 10

Related Questions