CODEWITHSUNDEEP
sqlsql-server
user3847554
user3847554

Reputation: 3

why do I get "arthemtic overflow error converting expression to data type int when converting the below code?

Create table COSTUMER(
[Costumer ID] int,
[Costumer Phone number] int,
[Costumer Date of Birth] int,
[Costumer Address] text,
[Costumer Name] varchar(40),
[Costumer Buisness if applicable] varchar(40)
);

I have created the ABOVE table with its relevant columns and rows and have tried inserting some data into the table by inserting the BELOW code but keep getting Msg 8115, Level 16, State 2, Line 1 "arthemtic overflow error converting expression to data type int" The statement has been terminated error.

I must be doing something wrong but I just don't see it :p

INSERT INTO COSTUMER 
      ([Costumer ID],[Costumer Phone number],[Costumer Date of Birth],[Costumer Address]
     ,[Costumer Name],[Costumer Buisness if applicable]) 
VALUES (24,07956485211,27/08/1993,'311 Fullwell avenue','Mohamed Ahmed','TESCO')

PLEASE HELP

Upvotes: 0

Views: 21122

Answers (3)

How 'bout a Fresca
How 'bout a Fresca

Reputation: 2317

The arithmetic overflow error is because you are trying to stuff data into a column whose datatype is not 'big' enough to hold the data you are trying to put into it. In other words, the maximum value for the datatype INT is 2,147,483,647 (reference below).

Depending on how the phone number is going to be used, I would store it as a bigint instead of an int. Bigint will take up 8 bytes of storage (same reference below), whereas for a phone number that is 11 digits (in your post 07956485211) a CHAR datatype will take up 11 bytes and a varchar will take up 13 bytes (reference below)

References:

http://msdn.microsoft.com/en-us/library/ms187745.aspx

http://msdn.microsoft.com/en-us/library/ms176089.aspx

Upvotes: 1

M.Ali
M.Ali

Reputation: 69554

Always you appropriate data types for the data you are storing. In your case you should use DATE or DATETIME datatype for Date of Birth Column.

Table Definition

Create table COSTUMER(
[Costumer ID]                     INT,
[Costumer Phone number]           VARCHAR(100),  --<-- mostly numbers has a leading Zero INT will not respect that
[Costumer Date of Birth]          DATE,          --<--  DATE data type
[Costumer Address]                VARCHAR(MAX),  --<-- avoid using text it has been deprecated in newer versions
[Costumer Name]                   varchar(40),
[Costumer Buisness if applicable] varchar(40)
);

INSERT Statement

INSERT INTO COSTUMER 
      ([Costumer ID],[Costumer Phone number],[Costumer Date of Birth],[Costumer Address]
     ,[Costumer Name],[Costumer Buisness if applicable]) 
VALUES (24,'07956485211','19930827','311 Fullwell avenue','Mohamed Ahmed','TESCO')

Note

Also use more than one column to store Address , Use columns like .....

AddressLine1 , AddressLine2, AddressLine3, County/Region, City, PostCode, Country

Your current schema violates the basic rules of database normilization.

Upvotes: 3

mikeo
mikeo

Reputation: 1075

This is because you're trying to insert a phone number as an INT. It should probably be a VARCHAR/NVARCHAR value.

And as user2989408 says, the Date of Birth should be a DATE or DATETIME.

Upvotes: 1

Related Questions