MySQL: Error 1264 Out of range value for a column

Out of range value for column – MySQL: Error 1264 Out of range value for a column

by

Out of range value for column: In this article we will discuss why Error code 1264 in MySQl occurs and how we can resolve it.

Overview :

MYSQL out of range value for column: This error arises mainly when strict SQL is enabled and the MySQL query stores a value in numeric column outside permissible range of column data type. If strict SQL is not enabled then MySQL server will clip the value appropriate to column data type range.

# to create the table
CREATE TABLE studentEnroll_data (
student_roll INT,
stud_name VARCHAR(50),
date_of_adm DATE,
student_sub_no INT,
fee_paid DECIMAL(8,2)
);
SELECT * FROM studentEnroll_data;

Output :

 

Error code 1264. out of range value for column at row 1

As studentEnroll_data is empty let’s insert some values to it. If we will try to save a value larger than maximum value of 1264 then will get the error saying-

For example: Write this in code – #1264 – Out Of Range Value For Column

Error Code: 1264. Out of range value for column ‘student_sub_no’ at row 1.

Likewise: Out Of Range Value For Column ‘Phone’ At Row 1.

We can remove the issue by storing a value less than maximum value of INT or change the data type of student_sub_no to BIGINT.

#Alter the table, modify the column student_sub_no
ALTER TABLE studentEnroll_data MODIFY student_ssn_no BIGINT ;
#insert rows into the table
INSERT INTO studentEnroll_data(student_roll,stud_name,date_of_adm,student_sub_no,fee_paid)
VALUES(1,"Rohit",'1999-06-22',321458795,12000.50),
(2,"Sourav",'1999-11-02',1447480374,12500.50),
(3,"Tarun",'1999-01-22',0197485789,32478.50),
(4,"Soumya",'1999-03-25',1145484791,25874.50),
(5,"Meghna",'1999-04-29',0144483594,12378.50),
(6,"Aditya",'1999-12-25',0142443794,25000.50),
(7,"Sahil",'1999-05-14',1447443791,32178.50);
SELECT * FROM studentEnroll_data;

Output :

Error code 1264. out of range value for column decimal :

 Let’s try to add one more row with value in fee_paid column as 125487495.50. But fee_paid column can only allow 10 digits including two decimal places. So we will get a error message as-

Error Code: 1264. Out of range value for column ‘fee_paid’ at row 1

To avoid this we can try inserting one more row by modifing the data type of column fee_paid to DECIMAL(10,2). So now columns can accept total 12 digits.

 #change the datatype of fee_submitted column
ALTER TABLE student_enroll_data MODIFY fee_paid DECIMAL(10,2) ;
#Inserting a row
INSERT INTO studentEnroll_data(student_roll,student_name,date_of_adm,student_sub_no,fee_paid)
VALUES(8,"Suresh",'1999-09-12',1267483794, 1958295862.12);
SELECT * FROM studentEnroll_data WHERE student_roll = 9;

Output

You can also read for more perfection in this topic.

  • Out Of Range Value For Column ‘Contact_No’ At Row 1
  • Numeric Value Out Of Range: 1264 Out Of Range Value For Column
  • Out Of Range Value For Column Decimal At Row 1
  • Sql Error 1264
  • Mysql Decimal Out Of Range
  • 1264 Out Of Range Value For Column
  • Error 1264 In Mysql
  • Out of range value mysql
  • Out Of Range Value For Column Sql