String to Decimal Conversion

Question

Post reply

String to Decimal Conversion

bunmiog

SSC-Addicted

Points: 448
More actions
May 18, 2006 at 6:09 pm

#76527

I am trying to load a text file using DTS and I am having problem with one of the decimal fields. The data is in the format 011953120 and the destination column is dec(10,7). Using VBscript I manipulated the source data as "01.1953120" convert it to double using CDbl and then assign it to the destination field, however what end up in the destination field after the upload is.1953120. I am loosing the data before the decimal point. I have tried it separate ways but no success. Any ideas how to achieve this.
Thanks

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

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

Scott-144766 SSCarpal Tunnel Points: 4333 More actions · Answer 1

Will something like

SELECT CAST(CAST('011953120' AS decimal(19,7))/10000000 AS decimal(10,7))

do the trick?

--
Scott

Erik Kutzler SSCarpal Tunnel Points: 4287 More actions · Answer 2

If you're using a data transformation task, use an ActiveX Script transformation for this specific tranformation.

Assuming the destination field in the database is named "YourDecimalValue" and the data is in column 2 of your source file, this code will do what you want:

Function Main()

DTSDestination("YourDecimalValue") = Left(DTSSource("Col002"), Len(DTSSource("Col002")) - 7) & "." & Right(DTSSource("Col002"), 7)

Main = DTSTransformStat_OK

End Function

bunmiog SSC-Addicted Points: 448 More actions · Answer 3

Thanks all for your suggestions. I solved the problem by first converting the value 011953120 to double decCnv = Cdbl(011953120). Then I divided the result by 10000000 and then assign the final result to my destination field defined as dec(10,7) and it worked.