4 Replies - 5166 Views - Last Post: 02 September 2010 - 04:01 AM Rate Topic: -----

#1 ibe333333  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 22
  • Joined: 11-August 09

DateTime - Error converting data type varchar to numeric.

Posted 22 August 2010 - 04:37 PM

I need some serious help with this SQL insert command, i cant seem to get the datetime to insert correctly, actually, it may be the entire insert command altogether, can anyone provide some insight on this? I am a complete newbie to sql, and i've googled for answers, and checked my book, but can't seem to find anything that will shed some light on this:

Just FYI, i am using SQL Server Management Studio Express 2008.
I have tried different things for the date, but the more i look at it, the more i feel that it really isnt the date

Heres the error: Error converting data type varchar to numeric.
Heres the SQL code:

CREATE TABLE dbo.BankClients(
	AccountID 	VARCHAR(10)	NOT NULL	PRIMARY KEY,
	FirstName	CHAR(20)	NOT NULL,
	LastName 	CHAR(20)	NOT NULL,
	Street1		VARCHAR(40)	NOT NULL,
	Street2		VARCHAR(40),
	City		CHAR(20)	NOT NULL,
	State		CHAR(2)		NOT NULL,
	ZipCode		INT		NOT NULL,
	PhoneNum	VARCHAR(12)	NOT NULL,
	Email		VARCHAR(50))

INSERT dbo.BankClients VALUES('100000001','Jack','Smith','88 Haven Road','','New Haven','CT',21345,'555-618-0001','jack.smith@email.com')

CREATE TABLE dbo.BankClients(
	AccountID 	VARCHAR(10)	NOT NULL	PRIMARY KEY,
	FirstName	CHAR(20)	NOT NULL,
	LastName 	CHAR(20)	NOT NULL,
	Street1		VARCHAR(40)	NOT NULL,
	Street2		VARCHAR(40),
	City		CHAR(20)	NOT NULL,
	State		CHAR(2)		NOT NULL,
	ZipCode		INT		NOT NULL,
	PhoneNum	VARCHAR(12)	NOT NULL,
	Email		VARCHAR(50))

INSERT dbo.BankClients VALUES('900000009','Jesus','Christ','10 Commandments Way','Suite 1','New Haven','CT',21345,'555-618-0009','jesus.christ@email.com')

CREATE TABLE dbo.BankTransactions(
	BankTransID	INT		NOT NULL	PRIMARY KEY,
	TransAccountID	VARCHAR(10)	references	BankClients(AccountID) NOT NULL,
	TransEmployeeID	VARCHAR(15)	references	Employees(EmployeeID) NOT NULL,
	Deposits	DEC(15,2)	NULL,
	Withdrawals	DEC(15,2)	NULL,
	TranDateTime	DateTime 	NOT NULL)

INSERT dbo.BankTransactions VALUES('1010101111','1000000001','10',1645.35,'','10-MAY-05')

This post has been edited by JackOfAllTrades: 22 August 2010 - 05:26 PM
Reason for edit:: Added code tags.


Is This A Good Question/Topic? 0
  • +

Replies To: DateTime - Error converting data type varchar to numeric.

#2 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6058
  • View blog
  • Posts: 23,495
  • Joined: 23-August 08

Re: DateTime - Error converting data type varchar to numeric.

Posted 22 August 2010 - 05:29 PM

BankTransID is an INTEGER, you're trying to insert it as a VARCHAR.

BankTransID	INT
...
INSERT dbo.BankTransactions VALUES('1010101111',...

Was This Post Helpful? 0
  • +
  • -

#3 ibe333333  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 22
  • Joined: 11-August 09

Re: DateTime - Error converting data type varchar to numeric.

Posted 22 August 2010 - 05:42 PM

View PostJackOfAllTrades, on 22 August 2010 - 04:29 PM, said:

BankTransID is an INTEGER, you're trying to insert it as a VARCHAR.

BankTransID	INT
...
INSERT dbo.BankTransactions VALUES('1010101111',...


Thanks! I tried fixing that too, but still getting errors, the two inserts works fine, the third insert command gives error: Msg 8114, Level 16, State 5, Line 37 Error converting data type varchar to numeric.

I've been thinking about this for 10 hours, haha seems sort of silly to be stuck on this, but at least i am giving it my all

GO
CREATE TABLE dbo.Employees(
	EmployeeID 	VARCHAR(15)	NOT NULL	PRIMARY KEY,
	FirstName	CHAR(20)	NOT NULL,
	LastName 	CHAR(20)	NOT NULL,
	Street1		VARCHAR(40)	NOT NULL,
	Street2		VARCHAR(40),
	City		CHAR(20)	NOT NULL,
	State		CHAR(2)		NOT NULL,
	ZipCode		INT		NOT NULL,
	PhoneNum	VARCHAR(12)	NOT NULL,
	Email		VARCHAR(50))

INSERT dbo.Employees VALUES('1','Jack','Smith','88 Haven Road','','New Haven','CT',21345,'555-618-0001','jack.smith@email.com')

CREATE TABLE dbo.BankClients(
	AccountID 	VARCHAR(10)	NOT NULL	PRIMARY KEY,
	FirstName	CHAR(20)	NOT NULL,
	LastName 	CHAR(20)	NOT NULL,
	Street1		VARCHAR(40)	NOT NULL,
	Street2		VARCHAR(40),
	City		CHAR(20)	NOT NULL,
	State		CHAR(2)		NOT NULL,
	ZipCode		INT		NOT NULL,
	PhoneNum	VARCHAR(12)	NOT NULL,
	Email		VARCHAR(50))

INSERT dbo.BankClients VALUES('110000011','Samuel','Winchester','44 Demon Way','','New Haven','CT',21345,'555-618-0010','samuel.winchester@email.com')

CREATE TABLE dbo.BankTransactions(
	BankTransID	INT		NOT NULL	PRIMARY KEY,
	TransAccountID	VARCHAR(10)	references	BankClients(AccountID) NOT NULL,
	TransEmployeeID	VARCHAR(15)	references	Employees(EmployeeID) NOT NULL,
	Deposits	DEC(15,2)	NULL,
	Withdrawals	DEC(15,2)	NULL,
	TranDateTime	DateTime 	NOT NULL)
	
INSERT dbo.BankTransactions VALUES(1010101111,'110000011','1',1645.35,'','20100813')

This post has been edited by JackOfAllTrades: 23 August 2010 - 03:56 AM
Reason for edit:: Added code tags.

Was This Post Helpful? 0
  • +
  • -

#4 ibe333333  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 22
  • Joined: 11-August 09

Re: DateTime - Error converting data type varchar to numeric.

Posted 22 August 2010 - 06:00 PM

View Postibe333333, on 22 August 2010 - 04:42 PM, said:

View PostJackOfAllTrades, on 22 August 2010 - 04:29 PM, said:

BankTransID is an INTEGER, you're trying to insert it as a VARCHAR.

BankTransID	INT
...
INSERT dbo.BankTransactions VALUES('1010101111',...


Thanks! I tried fixing that too, but still getting errors, the two inserts works fine, the third insert command gives error: Msg 8114, Level 16, State 5, Line 37 Error converting data type varchar to numeric.

I've been thinking about this for 10 hours, haha seems sort of silly to be stuck on this, but at least i am giving it my all

GO
CREATE TABLE dbo.Employees(
EmployeeID VARCHAR(15) NOT NULL PRIMARY KEY,
FirstName CHAR(20) NOT NULL,
LastName CHAR(20) NOT NULL,
Street1 VARCHAR(40) NOT NULL,
Street2 VARCHAR(40),
City CHAR(20) NOT NULL,
State CHAR(2) NOT NULL,
ZipCode INT NOT NULL,
PhoneNum VARCHAR(12) NOT NULL,
Email VARCHAR(50))

INSERT dbo.Employees VALUES('1','Jack','Smith','88 Haven Road','','New Haven','CT',21345,'555-618-0001','jack.smith@email.com')

CREATE TABLE dbo.BankClients(
AccountID VARCHAR(10) NOT NULL PRIMARY KEY,
FirstName CHAR(20) NOT NULL,
LastName CHAR(20) NOT NULL,
Street1 VARCHAR(40) NOT NULL,
Street2 VARCHAR(40),
City CHAR(20) NOT NULL,
State CHAR(2) NOT NULL,
ZipCode INT NOT NULL,
PhoneNum VARCHAR(12) NOT NULL,
Email VARCHAR(50))

INSERT dbo.BankClients VALUES('110000011','Samuel','Winchester','44 Demon Way','','New Haven','CT',21345,'555-618-0010','samuel.winchester@email.com')

CREATE TABLE dbo.BankTransactions(
BankTransID INT NOT NULL PRIMARY KEY,
TransAccountID VARCHAR(10) references BankClients(AccountID) NOT NULL,
TransEmployeeID VARCHAR(15) references Employees(EmployeeID) NOT NULL,
Deposits DEC(15,2) NULL,
Withdrawals DEC(15,2) NULL,
TranDateTime DateTime NOT NULL)

INSERT dbo.BankTransactions VALUES(1010101111,'110000011','1',1645.35,'','20100813')


ok, i just realized that i was using '' for the DEC datatype, when i should just use a zero, and i decided to do without the date, so when i ran this insert:

INSERT dbo.BankTransactions VALUES(1010101111,'1000000001','10',1645.35,0)

I get this error:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__BankTrans__Trans__6166761E". The conflict occurred in database "DataBaseProject", table "dbo.BankClients", column 'AccountID'.

Any insight on this, i dont really understand the concept of foreign keys

This post has been edited by ibe333333: 22 August 2010 - 06:01 PM

Was This Post Helpful? 0
  • +
  • -

#5 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: DateTime - Error converting data type varchar to numeric.

Posted 02 September 2010 - 04:01 AM

just a small change in your posted query
INSERT dbo.BankTransactions VALUES(1,'900000009','100000001',10,1645.35,'10-MAY-05')


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1