Function Required To Convert Packed Decimal In Binary(6) To Int (or Float)
Mar 8, 2007
I have been given some data from a Mainframe (AS400?) which has some fields coded in Packed Decimal. I have been able to load the data into a SQL2005 database table, but I now need to convert the Packed Decimal data in the binary(6) field to the appropriate integer (or float) value.
The field contains values such as the following:-
0x20202020200C
0x202020022025
0x20202020DFFA
I don't know how to interpret these. Has anyone got a function that can do this for me?
I've read several articles online that explain how packed decimal works, but none tell me how to interpret the last of my three examples. Can you help?
Thanks!
View 2 Replies
ADVERTISEMENT
Apr 9, 2007
I can't take full credit for this. I want to share this with Jeff Moden who did the important research for this calculation here.
All I did was just adapting some old code according to the mantissa finding Jeff made and optimized it a little
Some test codeDECLARE@SomeNumber FLOAT,
@BinFloat BINARY(8)
SELECT@SomeNumber = -185.6125,
@BinFloat = CAST(@SomeNumber AS BINARY(8))
SELECT@SomeNumber AS [Original],
CAST(@SomeNumber AS BINARY(8)) AS [Binary],
dbo.fnBinaryFloat2Float(CAST(@SomeNumber AS BINARY(8))) AS [Converted],
@SomeNumber - dbo.fnBinaryFloat2Float(CAST(@SomeNumber AS BINARY(8))) AS [Error]
And here is the code for the function.CREATE FUNCTION dbo.fnBinaryFloat2Float
(
@BinaryFloat BINARY(8)
)
RETURNS FLOAT
AS
BEGIN
DECLARE@Part TINYINT,
@PartValue TINYINT,
@Mask TINYINT,
@Mantissa FLOAT,
@Exponent SMALLINT,
@Bit TINYINT,
@Ln2 FLOAT,
@BigValue BIGINT
SELECT@Part = 1,
@Mantissa = 1,
@Bit = 1,
@Ln2 = LOG(2),
@BigValue = CAST(@BinaryFloat AS BIGINT),
@Exponent = (@BigValue & 0x7ff0000000000000) / EXP(52 * @Ln2)
WHILE @Part <= 8
BEGIN
SELECT@Part = @Part + 1,
@PartValue = CAST(SUBSTRING(@BinaryFloat, @Part, 1) AS TINYINT),
@Mask =CASE WHEN @Part = 2 THEN 8 ELSE 128 END
WHILE @Mask > 0
BEGIN
IF @PartValue & @Mask > 0
SET @Mantissa = @Mantissa + EXP(-@Bit * @Ln2)
SELECT@Bit = @Bit + 1,
@Mask = @Mask / 2
END
END
RETURNSIGN(@BigValue) * @Mantissa * POWER(CAST(2 AS FLOAT), @Exponent - 1023)
END
Thanks again Jeff!
Peter Larsson
Helsingborg, Sweden
View 3 Replies
View Related
Jan 10, 2012
I have a field in my database that is stored as varchar. The values are usually contain a decimal, and should have really been a float or decimal. In order for me to do analytics in my BI environment, I need to convert this to a float or decimal.
eg of values.
10.00
20.00
0.00
15.00
or could be missing when I use cast(value as float) or cast(value as decimal(9,2)) or convert(float, value) I get an error
Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to numeric.
View 2 Replies
View Related
May 18, 2006
I'm trying to move records from a SQL table with a float column to a DB2 database that has the column defined as Decimal (8,2) It keeps crashing saying it has a type mismatch problem. I tried changing my source command to pass in the column already converted and it still crashes on this. I also tried doing a data conversion task to do the conversion and I still get the same error. Any ideas?
View 3 Replies
View Related
Jun 3, 2002
Hi,
I need to write Stored Procedures to convert a Binary number to a Decimal number and Decimal back to Binary (i.e. 2 sp's).
I don't have a clue how I'm to do this. Can anyone help me !?
Pieter
View 1 Replies
View Related
Apr 10, 2008
Does anybody know
how to convert in SQL a number from decimal to binary:
Example: 'F' = 1111
I tried select convert(binary, 12.22) but SQL interprets the word 'binary' as Hex.
Thanks a lot!
View 10 Replies
View Related
Jul 23, 2005
Hello,I'm trying to decifer the data in the table that stores the data in thebinary format. All numbers are placed in varbinary fields. All I know is theMS SQL 2000 database useing collation SQL_Latin1_General_CP1_CI_AS(default).For example the content of the field is:(0xB4F5000000000000) in unicode and defined as varbinary(8).Are there any procedures that convert the unicode binary (or hexa) numbersback to ascii or readable form?I tried as following but it didn't work.select cast(0xB4F5000000000000 as decimal(8,2))Any help is appreciated,Adam
View 1 Replies
View Related
Jun 4, 2007
Hi,
I am having a file in which amount fields are given in a Packed Decimal format. Can anyone suggest me how I can read this data element from the file and convert it into SQL decimal datatype.
File is a fixed length. All the amount fields are given in Packed Decimal Format and rest of the fields are given in text format.
How can i identify and convert only those packed decimals using SQL/.Net.
Example : a row in a file that has some packed decimals
158203508540188236252EUR20BZK0030 œ&
20060715 0001010100010101
Please help!
Thanks
Mirudhu
View 4 Replies
View Related
Nov 26, 2007
Hello,
I'm trying to load en EBCDIC file with packed decimal in comp 3 into a SQL table.
I have been searching information in this forum in several threads and have downloaded a DLL from http://www.microsoft.com/downloads/details.aspx?familyid=0e4bba52-cc52-4d89-8590-cda297ff7fbd&displaylang=en
and so far I've been able to get the "UnpackDecimal Data Flow Transformation" to work ... sort of.
The problem is that in the destination table the decimals appear allways as NULL.
The destination decimal column type in the table is set as VARCHAR (I've tryed to set Integer with no success).
Any help on this will be apreciated. If you need more information please ask.
Thanks.
View 13 Replies
View Related
Mar 12, 2004
I have an stored procedure/DTS package that creates an output file that I FTP to a mainframe.
The vendor that is receiving the file expects negative amounts to be in a packed decimal format.
IE. Negative 95.46 = 0000954O
I've done a bit of searching to find a function for this, but I must be using the wrong words.
Any suggestions?
View 1 Replies
View Related
Dec 7, 2007
I need to do EBCDIC to ASCII conversion in SSIS. The incoming data has packed decimal fields in it. Has anyone been able to convert packed EBCDIC decimal fields to ACSCII using SSIS?
View 7 Replies
View Related
Apr 3, 2001
i have this flat text file that has a number of packed decimal
field type. How do I load that text file into a sql table.
thanks
View 4 Replies
View Related
Nov 4, 2015
I want to change decimal precision dynamically without rounding value
For example
10.56788 value for 2 decimal precision is 10.56.
10.56788 value for 3 decimal precision is 10.567.
---CASE 1 without dynamic parameter---------
DECLARE @DECIMALVALUE AS tinyint
SELECT CONVERT(DECIMAL(10,2),500000.565356) As Amount
[Code] ....
I am getting error as follows......
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '@DECIMALVALUE'
This decimal precision format value will vary company to company ...
View 7 Replies
View Related
Jun 29, 1998
We are having problems with rounding errors on large monetary calculations in sql server 6.5
The calculations include float fields (for volumes and unit of measure conversions in product movements). I was wondering if the float being "approximate" could be the problem.
IF it is, why would I want to store things as a float instead of a dec(28,14)?
Is it faster to compute numbers stored as approximate binaries? Will we see a big performance hit if we switch some of the table`s field`s to decimals?
Thanks in advance.
View 3 Replies
View Related
May 4, 2004
When should I choose decimal over float and vice versa?
Mike B
View 4 Replies
View Related
Nov 22, 2006
select convert(float,'1.2334e+006')1233400.0select convert(decimal(20,2),'1.2334e+006')Server: Msg 8114, Level 16, State 5, Line 1Error converting data type varchar to numeric.Is there any way around?Is there any set options? I tried arithabort or arithignore and theydon't work.Thanks.
View 2 Replies
View Related
Apr 29, 2008
Hello.
My database stores the decimals in Spanish format; "," (comma) as decimal separator.
I need to convert decimal nvarchar values (with comma as decimal separator) as a decimal or int.
Any Case using CAST or CONVERT, For Decimal or Int gives me the following error:
Error converting data type varchar to numeric
Any knows how to resolve.
Or any knows any parameter or similar, to indicate to the Cast or Convert, that the decimal separator is a comma instead a dot.
View 5 Replies
View Related
Sep 25, 2006
I am using sql express 2005 and sql server 2005 with C# 2.0.
I am a bit confused about which data type i should be using for several fields.
Right now I am declaring all of my fields in sql server as float for everything except for money fields which are using money.
When loaded into C# these fields are converted to double and decimal because C# does not have a float datatype.
Should I be using Decimal or Double for everything instead?
Here are a few examples
QtyInvoiced (float) - holds the number of items invoice
possible values look like this 1.0, 1.25 or 1.5
PercentDiscount (float) - holds a percentage
possible values look like this
10.25, 20.50, 50.00
I appreciate the help.
View 9 Replies
View Related
Jun 29, 2004
I have a float of 70.83333333343
If I do this
SET @Output=ROUND(@Output, 2, 1) -- @Output is DECLARED as FLOAT
I get this:
70.82999999999999
I want:
70.83
How do I do that?
Thanks in advance...
View 2 Replies
View Related
Dec 20, 2004
Hey,
I am filling a temp table with various float variables and I need to format one particular column to 2 decimal places.
Does anyone know the correct syntax to do this, and should it be done before filling the temp table or when I select what I needs from the temp table?
Thanks
View 3 Replies
View Related
Mar 24, 2008
How do I format the money or float field types to 2 decimal places during a SELECT statement?
View 4 Replies
View Related
Jul 3, 2014
I have a table with three columns: UniqID, Latitude, and Longitude.
I need to write a query to identify when the latitude has more than 6 decimal places past the decimal. Same with Longitude. Values in these attributes can be a negative number. These fields are FLOAT.
View 7 Replies
View Related
Jan 16, 2013
What is the difference between Money and (Float or Decimal) Datatype. If we use Float or Decimal instead of Money, will we loose any functions..?
View 4 Replies
View Related
Jun 21, 2007
Hi,
why does converting integer to float take so long? Its a column with about 5 Million rows.
I want to avoid cast(inumber1 as float) / cast(inmuber2 as float), thats why converting them. Queries should be a bit faster after that.. hope so :)
Thanks a lot
View 14 Replies
View Related
May 23, 2008
hi, how can I convert a float to a char so I can do:
'£' + convert(char,amount) AS [money]
When I do the above the number is like 1.07848e+006
rather than 1078480
thans for any help.
jamie
View 20 Replies
View Related
Apr 11, 2006
I have a sp that receive a rango of float values,and I need to convert this values to a varchar(20).
I am trying the next but I got a strange result.
@intNoTarjIni = 121456790
set @strTarjeta=cast(@intNoTarjIni as varchar(20))
returns: 1.21457e+008
set @strTarjeta=convert( varchar(20),@intNoTarjIni)
returns:1.21457e+008
How can I convert sucessfully a float to varchar?
View 2 Replies
View Related
Jun 26, 2007
Hi -
My field TDMergerVotePercent is defined as a float field. I want it to return 'NA' when the value is -1 but I'm getting the error message
'Error converting data type varchar to float' in my aspx page. Is there where the CAST function can be used? Thanks
CASE
WHEN TDMergerVotePercent = -1 THEN 'NA'
WHEN TDMergerVotePercent IS NULL THEN ''
ELSE TDMergerVotePercent
END AS TDMergerVotePercent
View 4 Replies
View Related
Feb 15, 2008
I have a data type float with a value of 10000487930 that I'm trying to insert into a data type nvarchar and am getting the result of '1.00005e+010'. I've tried cast(field as nvarchar) however this is not working. What might fix this? I cannot change the insert table data type.
View 3 Replies
View Related
Jul 23, 2005
Hello,I would like to convert a field from ntext field found in one databasetable to float field found in another database table. The reason why Iwant to do this is a long one.I have tried the following and playing around with the following:declare @valuePointer varbinary(16)<Row cursor logic to initialize @valuePointer to be a pointer to thesource ntext field>update TargetFloatTable set TargetFloatTable.TargetFloatValue =CAST(CAST(@valuePointer AS nvarchar) AS float)where TargetFloatTable.Id = @Idbut is not working for me.Hoping someone out there can help.Thanks,Cally
View 5 Replies
View Related
Aug 26, 2005
Select Cast('100.1234' as float)give me the result 100.1234Now when I convert it back to char I want exactly 100.1234Select Convert(char(100),Cast('100.1234' as float))Gives me 100.123 (Here I was expecting 100.1234)When I doSelect STR(Cast('100.1234' as float),25,4)I get back the result as 100.1234However here I am not sure how many digits do I have after the decimalpoint. If I put some value likeSelect STR(Cast('100.1234' as float),25,8)I get 0's appended to it, which is again not desired.Thanks in advance,Jai
View 4 Replies
View Related
Mar 7, 2008
Is this possible? I'm trying to user a lookup task and the data I want to compare is a varchar to float. How can I do this? I tried using the data conversion task and it didn't work and also tried cast and convert. Is this even possible or is there a way around it?
thanks,
View 14 Replies
View Related
Feb 12, 2007
I've three columns:
Length Width Height
1.5 2.5 10
2 3.7 19
in Query I want to display Like 1.5 X 2.5 X 10 (Length, Width, Height).....
???
View 1 Replies
View Related
Jan 23, 2002
I'm running the following statement...
The column is currently float (8). Need to convert to numeric. I've tried cast, convert. no go for either.
Any help on this would be greatly appreciated.
..... select ... convert(numeric(38,0), colname)
TIA,
Jeremy
View 1 Replies
View Related
