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

Question

INSERT dbo.Customers
(FirstName,LastName, Email, DOB, Phone)
VALUES
('John', 'Smith', '[e-mail]', 2-4-1968, 626222-2222),
('Steven', 'Goldfish', '[e-mail]', 4-4-1974, 323455-4545),
('Paula', 'Brown', '[e-mail]', 5-24-1978, 416323-3232),
('James', 'Smith', '[e-mail]', 20-10-1980, 416323-8888)

Operand type clash: int is incompatible with date

Answer 1

@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

Answer 2

1-Or date format is not correct.
2- DOB is define like int and you must to change that type for date type

Answer 3

DOB data? or DOB date???

Answer 4

you fix it, maybe you must to fix in database too

Answer 5

;-)

Answer 6

@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?

Answer 7

First you post CREATE ..... with data and not with date so I could think you really created your database with wrong type, right?

Answer 8

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', '[e-mail]', '1968-4-2', '626 222-2222'),
('Steven', 'Goldfish', '[e-mail]', '1974-4-4', '3234 55-4545'),
('Paula', 'Brown', '[e-mail]', '1978-05-24', '416 323-3232'),
('James', 'Smith', '[e-mail]', '1980-10-20', '416 323-8888')

How to add / instead of - with inserting the date in sql server?

Answer 9

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', '[e-mail]', 2/4/1968, 626222-2222),
('Steven', 'Goldfish', '[e-mail]', 4/4/1974, 323455-4545),
('Paula', 'Brown', '[e-mail]', 5/24/1978, 416323-3232),
('James', 'Smith', '[e-mail]', 20/10/1980, 416323-8888)

Operand type clash: int is incompatible with date

Answer 10

date

Answer 11

How?

Answer 12

I'll run it now

Answer 13

thanks, I'll try it now