January 11, 2006 at 12:17 pm
I download some information from a vendor ftp site and run it through a DTS package into a database. One of the fields contains unit cost and reads '00001234', on the vendor text file. I want it to read '0000.1234' in my database. When I designed the table I assigned a Decimal data type with a Precision of 8 and a Scale of 4.
After running the DTS package the database shows '1234.0000' for this field.
Any suggestions?
January 11, 2006 at 12:34 pm
'00001234' converted to a number will yeild 1234. How about dividing by 10000 on the file import?
January 11, 2006 at 12:52 pm
Dividing by 10,000 is certainly possible, I'm just hoping to eliminate extra steps. I was hoping that identifying a field with 4 decimals would make it appear as '0000.1234 ' in the database, or by saying the field has 3 decimals would make it appear as '00000.123'. If my assumption is incorrect then division is the only solution.
January 11, 2006 at 12:59 pm
Nope, SQL Server knows nothing about the source system generating this import data and has no way of knowing that the source system has apparently multiplied the data by 10,000 during extraction.
The value '00001234' is a whole number, and it would be a serious bug if SQL Server assumed a divisor based on the data type of the column you insert it to.
January 13, 2006 at 1:27 am
Hi Mark,
If the source field is consistently 8 digits, with 4 digits on either side of the point, I would import it as a Char and then add the point, something like.
SELECT CAST( LEFT(a.MyField,4) + '.' + RIGHT(a.MyField,4) AS DECIMAL(4,4) ) FROM a
Hope this helps.
(Marvin)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply