0

How fix Microsoft sql server error: Operand type clash: int is incompatible with date

INSERT dbo.Customers (FirstName,LastName, Email, DOB, Phone) VALUES ('John', 'Smith', 'John.Smith@yahoo.com', 2-4-1968, 626222-2222), ('Steven', 'Goldfish', 'goldfish@fishere.net', 4-4-1974, 323455-4545), ('Paula', 'Brown', 'pb@herowndomain.org', 5-24-1978, 416323-3232), ('James', 'Smith', 'jim@supergig.co.uk', 20-10-1980, 416323-8888) Operand type clash: int is incompatible with date

20th Oct 2017, 9:45 PM
Peter Coker
Peter Coker - avatar
13 odpowiedzi
+ 4
@Peter, SQL Server misunderstood your query and assumed 2-4-1968 and 2/4/1968 as arithmetic expression to calculate, hence it then push in the calculation result (int) into your <DOB>, which, actually is a date field, resulting in query failure, conversion from int to date failed. Use a string to represent the date in ISO format ('yyyy-mm-dd'), it is acceptable in most database engines, including SQL Server, the only exception to this is, if SQL Server itself is configured to accept date input using custom format. On a rare occasion, you may find the escape route by using 'yyyymmdd' format, which is a raw date string, unlike general date format, it skips the - or / separator, but it is not your case. Finally, I would also recommend you to use varchar instead of bigint for <Phone> field, as I see it, your query showed me that you are making the same mistake there, the value of <Phone> field will be the calculation result of your input instead of the actual phone number :) Hth, cmiiw
21st Oct 2017, 4:31 AM
Ipang
+ 3
1-Or date format is not correct. 2- DOB is define like int and you must to change that type for date type
20th Oct 2017, 10:09 PM
Daniel
Daniel - avatar
+ 2
DOB data? or DOB date???
20th Oct 2017, 10:25 PM
Daniel
Daniel - avatar
+ 2
you fix it, maybe you must to fix in database too
20th Oct 2017, 10:34 PM
Daniel
Daniel - avatar
+ 2
;-)
20th Oct 2017, 10:42 PM
Daniel
Daniel - avatar
+ 2
@Peter, I've never used '/' for inserting date before, so I don't know, maybe you should try it out, yet still, use YMD ordering, so try it like '1968/4/2', post back here again about the outcome will you?
21st Oct 2017, 5:23 PM
Ipang
+ 1
First you post CREATE ..... with data and not with date so I could think you really created your database with wrong type, right?
20th Oct 2017, 10:38 PM
Daniel
Daniel - avatar
+ 1
IT WORKED..... I'll liked to you two for helping, I'm sure just a rookie devopler hanged on software engineer. CREATE TABLE Customers (CustomersId int NOT NULL IDENTITY (1,1) PRIMARY KEY, FirstName varchar(20) NOT NULL, LastName varchar(50) NOT NULL, Email varchar(50) NOT NULL, DOB date NOT NULL, Phone varchar(50) NOT NULL ) INSERT dbo.Customers (FirstName,LastName, Email, DOB, Phone) VALUES ('John', 'Smith', 'John.Smith@yahoo.com', '1968-4-2', '626 222-2222'), ('Steven', 'Goldfish', 'goldfish@fishere.net', '1974-4-4', '3234 55-4545'), ('Paula', 'Brown', 'pb@herowndomain.org', '1978-05-24', '416 323-3232'), ('James', 'Smith', 'jim@supergig.co.uk', '1980-10-20', '416 323-8888') How to add / instead of - with inserting the date in sql server?
21st Oct 2017, 4:52 PM
Peter Coker
Peter Coker - avatar
0
CREATE TABLE Customers (CustomersId int NOT NULL IDENTITY (1,1) PRIMARY KEY, FirstName varchar(20) NOT NULL, LastName varchar(50) NOT NULL, Email varchar(50) NOT NULL, DOB date NOT NULL, Phone bigint NOT NULL) INSERT dbo.Customers (FirstName,LastName, Email, DOB, Phone) VALUES ('John', 'Smith', 'John.Smith@yahoo.com', 2/4/1968, 626222-2222), ('Steven', 'Goldfish', 'goldfish@fishere.net', 4/4/1974, 323455-4545), ('Paula', 'Brown', 'pb@herowndomain.org', 5/24/1978, 416323-3232), ('James', 'Smith', 'jim@supergig.co.uk', 20/10/1980, 416323-8888) Operand type clash: int is incompatible with date
20th Oct 2017, 10:20 PM
Peter Coker
Peter Coker - avatar
0
date
20th Oct 2017, 10:31 PM
Peter Coker
Peter Coker - avatar
0
How?
20th Oct 2017, 10:36 PM
Peter Coker
Peter Coker - avatar
0
I'll run it now
20th Oct 2017, 10:39 PM
Peter Coker
Peter Coker - avatar
0
thanks, I'll try it now
21st Oct 2017, 5:41 PM
Peter Coker
Peter Coker - avatar