Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2013
    Posts
    3

    Unanswered: Operand type clash: int is incompatible with date

    I'm getting this error when trying to insert data into a table.
    "Operand type clash: int is incompatible with date"

    This is the SQL I used to create the table:
    Code:
    CREATE TABLE customers(
    	customer_id int IDENTITY(1,1) NOT NULL,
    	member_yn char(1) NOT NULL,
    	membership_number int NOT NULL,
    	date_became_member date NOT NULL,
    	customer_first_name varchar(20) NOT NULL,
    	customer_last_name varchar(20) NOT NULL,
    	customer_address varchar(128) NOT NULL,
    	customer_phone int NOT NULL,
    	customer_email varchar(128) NOT NULL,
    	customer_dob date NOT NULL);
    This is the SQL I am using to add data: (Note that there are two dates in each line, scroll to the side.)
    Code:
    INSERT INTO customers VALUES ('y', 156, 2010-08-29, 'John', 'Smith', '1235 Main Street, Dunmore, PA 18512', 6963287654, 'jsmith@hotmail.com', 1986-06-23);
    INSERT INTO customers VALUES ('y', 159, 04/15/2011, 'Mary', 'Jones', '235 Ardmore Blvd, Pittsburgh, PA 15221', 3429831594, 'jones_mary@gmail.com', 01/12/1992);
    Each of the lines above to add data uses a different data format because I tried both, while commenting out the other, hoping for the problem to go away with the change in format.

    It has not though.

    Does anyone know what I'm doing wrong? I probably missed something easy. but I'm a student new to databases and have been unable to get this to work.
    Also, there are other tables in the database that I excluded because of all the code. I can post that if needed.

    Here is the SQL code to create a primary key for this table:
    Code:
    ALTER TABLE customers ADD CONSTRAINT pk_customers PRIMARY KEY (customer_id);
    Thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    INSERT INTO customers VALUES ('y', 156, '2010-08-29', 'John', 'Smith'
    ,  '1235 Main Street, Dunmore, PA 18512', 6963287654, 'jsmith@hotmail.com'
    ,  '1986-06-23');
    INSERT INTO customers VALUES ('y', 159, '04/15/2011', 'Mary', 'Jones'
    ,  '235 Ardmore Blvd, Pittsburgh, PA 15221', 3429831594, 'jones_mary@gmail.com'
    ,  '01/12/1992');
    I put quotes around your integer math expressions to make them into dates.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Apr 2013
    Posts
    3
    Just added quotes to all dates, now I'm getting a new error:
    Msg 8115, Level 16, State 2, Line 126
    Arithmetic overflow error converting expression to data type int.
    The statement has been terminated.
    Msg 8115, Level 16, State 2, Line 127
    Arithmetic overflow error converting expression to data type int.
    The statement has been terminated.
    I shortened the phone numbers and this problem went away. So the phone numbers are too long. How can I store them and avoid the overflow error? Quotes didn't work either.
    Last edited by cmorris14; 04-18-13 at 22:49.

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Phone numbers are not integers

    You do not do math on phone numbers; they are strings. Look up the E.164 standard for phone numbers and use it. If you go international now, it will save you a lot of trouble later.

    Also, the only ISO-8601 display format in ANSI/ISO SQL is "yyyy-mm-dd" and Microsoft is moving that way, too.

  5. #5
    Join Date
    Apr 2013
    Posts
    3
    Quote Originally Posted by Celko View Post
    You do not do math on phone numbers; they are strings. Look up the E.164 standard for phone numbers and use it. If you go international now, it will save you a lot of trouble later.

    Also, the only ISO-8601 display format in ANSI/ISO SQL is "yyyy-mm-dd" and Microsoft is moving that way, too.
    Thanks. I'll change the phone number data type.

    A further question about the date format. I am using SQL Server 2008 R2. You said Microsoft was moving towards ISO-8601, but will it work with SQL Server 2008 R2? This doesn't need to be future-proof, it's an example, not a database that I will be using for a business.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •