[dba-SQLServer]Converting character value to small Money

Nicholson, Karen knicholson at gpsx.net
Tue Oct 7 10:50:14 CDT 2003


Help, since we were talking about casting and converting, somewhat.  I have
an sp put together to take data that was imported from a csv file.  All
fields are in a table stored as varchar 255.  It is choking on the ones that
need to be converted to money.  This is my syntax: (I followed the online
books syntax, I thought, for converting varchar to moola.  I have
highlighted where I think it is choking and included the error message).
Thanks.

CREATE PROCEDURE gps_convert AS
drop table gps_archive_request_data

create table gps_archive_request_data(
jrnlsrc_id char(4), 
batch_no int, 
journal_no int, 
journal_seqno int, 
co_no smallint, 
period_no tinyint, 
period_year smallint,
dist_gl int, 
dist_sub int,
branch_no int, 
tran_amt money, 
tran_date smalldatetime, 
trantype_id char(6), 
post_date varchar(25), 
change_user int, 
change_date smalldatetime, 
invoice_no int, 
invline_no smallint, 
reference varchar(20), 
invtry_qty smallmoney, 
revdet_no smallint, 
costctr_id varchar(6), 
gl_posted_date smalldatetime, 
payctrl_no int, 
payline_no smallint, 
revsum_no int, 
job_no int, 
jobline_no smallint, 
cust_no int, 
site_no int, 
site_branch_no int, 
)

insert gps_archive_request_data(
jrnlsrc_id, 
batch_no, 
journal_no, 
journal_seqno, 
co_no, 
period_no, 
period_year,
dist_gl, 
dist_sub,
branch_no, 
tran_amt, 
tran_date, 
trantype_id, 
post_date, 
change_user, 
change_date, 
invoice_no, 
invline_no, 
reference, 
invtry_qty, 
revdet_no, 
costctr_id, 
gl_posted_date, 
payctrl_no, 
payline_no, 
revsum_no, 
job_no, 
jobline_no, 
cust_no, 
site_no, 
site_branch_no
)

select
convert(char(4), jrnlsrc_id),
convert(int, batch_no),
convert(int, journal_no),
convert(int, journal_seqno),
convert(smallint,co_no),
convert(tinyint, period_no),
convert(smallint, period_year),
convert(int, dist_gl),
convert(int, dist_sub),
convert(int, branch_no),
cast(tran_amt as money),  /* This is choking*/
convert(smalldatetime,tran_date),
convert(char(6),trantype_id),
convert(varchar(25),post_date),
convert(int,change_user),
convert(smalldatetime,change_date),
convert(int,invoice_no),
convert(smallint, invline_no),
convert(varchar(20), reference),
cast(invtry_qty as smallmoney), /*This is choking*/
convert(smallint,revdet_no),
convert(varchar(6), costctr_id),
convert(smalldatetime,gl_posted_date),
convert(int,payctrl_no),
convert(smallint,payline_no),
convert(int,revsum_no),
convert(int, job_no),
convert(smallint,jobline_no),
convert(int, cust_no),
convert(int,site_no),
convert(int, site_branch_no)
from
gps_gl_archive


This is my error message:
Changed language setting to us_english.
Server: Msg 293, Level 16, State 1, Procedure gps_convert, Line 36
Cannot convert char value to smallmoney. The char value has incorrect
syntax.


More information about the dba-SQLServer mailing list