Converting to decimal datatype

Question

Converting to decimal datatype

BG0AWI

Old Hand

Points: 349
More actions
November 6, 2001 at 8:20 am

#77916

We have dataflex-files which is to be loaded into some sql-server tables. There are some columns which holds different kind of prices.
The format in the dataflex-table columns is like 100,0000. When we're trying to load this into a column of the data-type decimal(10,2) something goes wrong. If the format instead was 100.0000 there would have been no problems. The problem is the comma-delimiter. What kind of datatype should we use?

AW

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Henrik Staun Poulsen SSCertifiable Points: 6592 More actions · Answer 1

You should be able to set your DataFlex such that it exports its data in the 100.1234 format. Just run DFSETUP, and change it.

Take a backup of termlist.cfg (or dfini.cfg if you're on a newer DataFlex) before you start.

I have just converted a total of 23 million records in various tables into MS SQL7. It runs ok.

If you have any problems, write me, or visit the newsgroups at http://www.dataaccess.com.

Best regards

Henrik Staun Poulsen

Stovi Software

Denmark

Steve Jones - SSC Editor SSC Guru Points: 737892 More actions · Answer 2

That sounds like the best solution. You could always use an ActiveX conversion to cast the data into a decimal format, but it would probably be slower.

Steve Jones

steve@dkranch.net

don1941 SSCarpal Tunnel Points: 4183 More actions · Answer 3

You can also do a 2-step: import the data into (var)char field, then use the REPLACE() function to strip out the commas while converting to your decimal field.

update imported

set decField = cast(replace(CharField, ',', '') as decimal(10,4))

don1941 SSCarpal Tunnel Points: 4183 More actions · Answer 4

You can also do a 2-step: import the data into (var)char field, then use the REPLACE() function to strip out the commas while converting to your decimal field.

update imported

set decField = cast(replace(CharField, ',', '') as decimal(10,4))

Converting to decimal datatype

Cookies on SQLServerCentral