Operand type clash int is incompatible with date The INSERT statement conflicted with the FOREIGN KEY constraint

Question

create table pilot   emp num int  pl license varchar  3   pl ratings varchar  30   pl med type int  pl med date date  pl pt135 date date  constraint PK pilot primary key  emp num     insert into pilot emp num pl license pl ratings pl med type pl med date pl pt135 date  values  101  ATP   SEL MEL instr CFII  1 12-4-2005 15-6-2005  insert into pilot emp num pl license pl ratings pl med type pl med date pl pt135 date  values  104  ATP   SEL MEL instr  1 10-5-2005 23-3-2006  insert into pilot emp num pl license pl ratings pl med type pl med date pl pt135 date  values  105  COM   SEL MEL instr CFI  2 25-2-2006 12-2-2005  insert into pilot emp num pl license pl ratings pl med type pl med date pl pt135 date  values  106  COM   SEL MEL instr  2 02-4-2006 24-12-2005  insert into pilot emp num pl license pl ratings pl med type pl med date pl pt135 date        values  109  COM   SEL MEL instr CFII  1 14-4-2006 21-4-2006    My question is there is an error in every insert      Operand type clash  int is incompatible with date   How to fix this         Also here       create table employee   emp num int  constraint PK employee primary key  emp num   foreign key emp num  references pilot emp num   emp title varchar  4   emp lname varchar  20   emp fname varchar  30   emp initial varchar  2   emp dob date  emp hire date date     insert into employee emp num emp title emp lname emp fname emp initial emp dob emp hire date  values  100  Mr    Kolmycz   George   D  15-5-1942 15-3-1987  insert into employee emp num emp title emp lname emp fname emp initial emp dob emp hire date  values  101  Ms    Lewis   Rhonda   G  19-3-1965 25-4-1988  insert into employee emp num emp title emp lname emp fname emp initial emp dob emp hire date  values  102  Mr    Vandam   Rhett      14-11-1958 20-12-1992  insert into employee emp num emp title emp lname emp fname emp initial emp dob emp hire date  values  103  Ms    Jones   Anne   M  16-10-1974 28-8-2005  insert into employee emp num emp title emp lname emp fname emp initial emp dob emp hire date  values  104  Mr    Lange   John   P  08-11-1971 20-10-1996  insert into employee emp num emp title emp lname emp fname emp initial emp dob emp hire date  values  105  Mr    williams   Robert   D  14-3-1975 08-1-2006  insert into employee emp num emp title emp lname emp fname emp initial emp dob emp hire date  values  106  Mrs    Duzak   Jeanine   K  12-2-1968 05-1-1991  insert into employee emp num emp title emp lname emp fname emp initial emp dob emp hire date  values  107  Mr    Diante   Jorge   D  21-8-1974 05-1-1991  insert into employee emp num emp title emp lname emp fname emp initial emp dob emp hire date  values  108  Mr    Wlesenbach   Paul   R  14-2-1966 18-11-1994  insert into employee emp num emp title emp lname emp fname emp initial emp dob emp hire date  values  109  Ms    Travis   Elizabeth   K  18-6-1961 14-4-1991  insert into employee emp num emp title emp lname emp fname emp initial emp dob emp hire date  values  110  Mrs    Genkazi   Leighla   W  19-5-1970 01-12-1992               Msg 547  Level 16  State 0  Line 1   The INSERT statement conflicted with the FOREIGN KEY constraint  FK  crew  emp num  0F975522   The conflict occurred in database  melisa   table  dbo employee   column  emp num   error on   this table   create table earndrating  emp num int  constraint PK earndarating primary key emp num  rtg code   rtg code varchar 6   foreign key  emp num  references pilot emp num   foreign key rtg code  references rating rtg code   earningth date varchar 20         insert into earndrating emp num rtg code earningth date      values 101  CFI   18-Feb-98        insert into earndrating emp num rtg code earningth date      values 101  CFII   14-Dec-05        insert into earndrating emp num rtg code earningth date      values 101  INSTR   08-Nov-93        insert into earndrating emp num rtg code earningth date      values 101  MEL   23-Jun-94        insert into earndrating emp num rtg code earningth date      values 101  SEL   21-Apr-93        insert into earndrating emp num rtg code earningth date      values 104  INSTR   14-Jul-96        insert into earndrating emp num rtg code earningth date      values 104  MEL   29-Jan-97        insert into earndrating emp num rtg code earningth date      values 104  SEL   12-Mar-95        insert into earndrating emp num rtg code earningth date      values 105  CFI   18-Nov-97        insert into earndrating emp num rtg code earningth date      values 105  INSTR   17-Apr-95        insert into earndrating emp num rtg code earningth date      values 105  MEL   12-Aug-95        insert into earndrating emp num rtg code earningth date      values 105  SEL   23-Sep-94        insert into earndrating emp num rtg code earningth date      values 106  INSTR   20-Dec-95        insert into earndrating emp num rtg code earningth date      values 106  MEL   02-Apr-95        insert into earndrating emp num rtg code earningth date      values 105  SEL   10-Mar-94        insert into earndrating emp num rtg code earningth date      values 109  CFI   05-Nov-98        insert into earndrating emp num rtg code earningth date      values 109  CFII   21-Jun-03        insert into earndrating emp num rtg code earningth date      values 109  INSTR   23-Jul-96        insert into earndrating emp num rtg code earningth date      values 109  MEL   15-Marc-97        insert into earndrating emp num rtg code earningth date      values 109  SEL   05-Feb-96        insert into earndrating emp num rtg code earningth date      values 109  SES   12-May-96

User · Answer

FYI this turned out to be an issue for me where I had two tables in a statement like the following:

SELECT * FROM table1
UNION ALL
SELECT * FROM table2

It worked, but then somewhere along the line the order of columns in one of the table definitions got changed. Changing the * to SELECT column1, column2 fixed the issue. No idea how that happened, but lesson learned!

User · Answer

This expression 12-4-2005 is a calculated int and the value is -1997  You should do like this instead  2005-04-12  with the   before and after

User · Answer

I had the same problem  I tried  yyyy-mm-dd  format i e   2013-26-11  and got rid of this problem

User · Answer

Try wrapping your dates in single quotes  like this    15-6-2005    It should be able to parse the date this way

[sql] Operand type clash: int is incompatible with date + The INSERT statement conflicted with the FOREIGN KEY constraint

Examples related to sql

Examples related to sql-server

Examples related to tsql