Home » SQL

SQL query to change column datatype

SQL ALTER TABLE Example: Here, we are going to learn how to change the column data types in SQL?
Submitted by Abhishek Goel, on April 26, 2020

Datatype of the column in SQL can be changed using the ALTER TABLE command. It can also be used to ADD, DELETE or MODIFY columns in already existing tables. It can also be used to ADD or DROP constraints in the SQL database.

Here we are going to only talk about changing the datatype of the column.

For doing the task the following syntax is used,

    ALTER TABLE table_name
    MODIFY COLUMN column_name datatype;

Let's understand the above-mentioned concept with the help of example.

Let us consider the table "People".

SR	NAME	AGE	BIRTH_YEAR
1	Mona	45	1975
2	Kanishka	18	2002
3	Hardik	22	1998

Here we are going to change the datatype for birth_year from int to year datatype.

SQL Query:

ALTER TABLE People
MODIFY COLUMN birth_year year;

After the commitment of this program code, the column birth_year is now of type year and can only store birth_year in the two or four-digit format.