Date Conversion Issue

ilfoprog · #1 (**permalink**) 11-28-11, 16:16

Hello programmers. I was running a SP for the past couple of years. I dropped it to create it again with an additional column included (cmpcode). Now I get the below error message. Can someone help me with resolving this? Thank you. See attached.

CODE:
CREATE PROC BLTextData

@pay_date DATETIME

AS

DELETE zoas_apcheckinfo

INSERT zoas_apcheckinfo
SELECT cmpcode,
invno,
vendor,
paydate,
doccode,
checkno,
invamt,
checkamt,
status,
duedate,
year,
period,
docdate,
vendorname,
docnum

FROM oas_apcheckinfo
WHERE paydate = @pay_date
ORDER BY vendor

ERROR:
Msg 257, Level 16, State 3, Procedure BLTextData, Line 11
Implicit conversion from data type datetime to smallint is not allowed. Use the CONVERT function to run this query.

PracticalProgram · #2 (**permalink**) 11-28-11, 16:53

So the error says that you are trying to push a datetime value into a smallint field.

Is it possible that you are accidentally doing that???

ilfoprog · #3 (**permalink**) 11-28-11, 19:26

Ok, thanks for the reply. I figured it out. I had one of my columns out of order.

Wim · #4 (**permalink**) 11-30-11, 03:43

You just found out why it is better to write INSERT scrpits like this:

Code:

INSERT zoas_apcheckinfo (cmpcode, invno, vendor,
   paydate, doccode, checkno,
   invamt, checkamt, status,
   duedate, year, period,
   docdate, vendorname, docnum)
SELECT cmpcode, invno, vendor,
   paydate, doccode, checkno,
   invamt, checkamt, status,
   duedate, year, period,
   docdate, vendorname, docnum
FROM oas_apcheckinfo

Internet Services

Web Development

Digital Marketing

Consumer Tech