Decimal places in Data Conversion Transformation Editor

Question

Decimal places in Data Conversion Transformation Editor

brad.luettke

Mr or Mrs. 500

Points: 515
More actions
May 21, 2010 at 4:51 am

#232683

Hello,
I'm trying to get an input from a file to display within SQL Server with 2 decimal places in from the right. For example the input file displays as +00002322 which I want to display within sql server as 23.22. At the moment, whenever I choose a Data Type that will work, it appears as 2322.00. I've tried changing the Data Type to numeric, float and decimal as well as a few others to see if I can work out which one gives me a decimal option, but to no avail so far so I maybe missing something.
Is there anyway like an input mask or something similar I can set that will tell the data conversion to always have the decimal point between the 6th and 7th characters? I've had a search round the forum and have found a post with a similar problem:
But I'm unable to work out how to use it within an SSIS package and the Data Conversion function.
Thanks

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

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

Has the number that comes from the input file always two digits after the decimal point?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

brad.luettke Mr or Mrs. 500 Points: 515 More actions · Answer 2

Hi,

Yes, looking at the output file we have and how its currently displayed in its current SQL 2000 table, the last 2 digits appear after the decimal point. If both are 00 then it just displays the full figure with no decimal. So -00045544 is currently appearing as -455.44 in 2000, but -45544.00 in 2005, or 00024200 appearing as 242 in 2000 and 24200.00 in 2005.

These are all prices, and the majority of these on the input file appear as 8 characters, but there is one field where it is only 6 characters, but the principle remains the same as in the the last 2 digits need to appear after the decimal point.

Thanks

Phil Parkin SSC Guru Points: 246965 More actions · Answer 3

Could you just divide it by 100?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

brad.luettke Mr or Mrs. 500 Points: 515 More actions · Answer 4

Yep thats just dawned on me!! Used the derived column function to facilitate that and all works ok. Thanks.

Phil Parkin SSC Guru Points: 246965 More actions · Answer 5

Glad it worked.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin