[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 465
  • Last Modified:

converting varchar to money part 2

Hello experts:

I orginally asked this question here:  http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21708078.html


Since then I'm having problems with this DTS.  It now fails on the line I use to convert my weird "0*100*0" rows to true money fields.  
This is the error I get now:  Cannot convert a char value to money. The char value has incorrect syntax.

This worked back when I first tried it and now I'm coming back to this DTS package and its failing on these conversions.  Any ideas?  I scanned the entire table looking for something out of the ordinary and I found nothing.  The fields in my temp table are in varchar format.  Final table is set to money.  

help!

0
jay-are
Asked:
jay-are
  • 4
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
What is the contents of the file?
Is the DTS trying to convert to money into the temp table directly?
are there any NULL values?
0
 
illCommented:
-- please, check rows returned from this
select * from SERVICEINVOICE
where isnumeric(cast(replace(replace(TXBL,'0*',''),'*0','') as money) / 100) <>1
0
 
jay-areAuthor Commented:
angelIII:  You can see some examples of what's in the file in the link above.  The DTS is trying to convert to money on the insert/update statements.  Convert from the temp table to the final table.  I checked through 16k rows for anything out of the ordinary and couldn't find it.  I found one amount that was "23000*0*23500".  I didn't know if that would screw with the replace so I deleted that specific row and the insert still failed...

ill:  I will run your select shortly.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
illCommented:
--when i'm looking at the statement now, i can see, that it solves nothing, as it already contains convert. This one may help you to detect "bad" lines
select * from SERVICEINVOICE
where isnumeric(replace(replace(TXBL,'0*',''),'*0','') ) <>1
0
 
jay-areAuthor Commented:
Ok ill, I ran your select and it comes up with a line where the txbl field is "1450*0*45522"

I ran this across the 2 other columns that are using this same convert and I came up with more.  There are 3 lines in total that have more than one amount in these "0*0*0" columns...How can I get around this?
0
 
jay-areAuthor Commented:
How difficult would it be to add the amounts together in these 3 lines?
50*0*100 to 1.50
etc

??
It seems to be a data error.  The user entered the amounts on the wrong line so I have these 3 lines with bad amounts...
0
 
jay-areAuthor Commented:
Any ideas?
0
 
illCommented:
-- one more replace is needed as there is no "*0" in the field after replacing "0*"
update SERVICEINVOICE_final set TXBL = convert(money,cast(replace(replace(replace(serviceinvoice.TXBL,'0*',''),'*0',''),'*', '') as money) / 100,1) from serviceinvoice where serviceinvoice.ronum = serviceinvoice_final.ronum
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now