By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,399 Members | 984 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,399 IT Pros & Developers. It's quick & easy.

Binary to Decimal conversion

P: 25
Hello;

I am reading a file that is ODBC-linked directly to an AS400 machine in a VBscript program within Access. One of the fields in this file automatically gets defined as a Binary data type in the Access table. On the AS400, the field contains a 6-byte date: 080822 (fmt= yymmdd). When I look at this through the VBscript debugger, I can see that the values are as follows:

value(0): 240
value(1): 248
value(2): 240
value(3): 248
value(4): 242
value(5): 242

How can I convert these values into their decimal equivalents of 0, 8, 0, 8, 2, 2?

Is it as simple as subtracting 240 from each one? And, if so, what is the technical reason behind 240?

thanks,
Gregg
Aug 23 '08 #1
Share this Question
Share on Google+
7 Replies


Dököll
Expert 100+
P: 2,364
Hey there Gregg!

I will send over to Access forum for a closer look... Will pop in if I find anything, just passing through as of now.

Here:
http://www.google.com/search?hl=en&r...&start=10&sa=N

Actually I did a quick Google on this for ya, no real hits, but thought you could have a look while you wait:-)

Later Gregg!
Aug 24 '08 #2

ADezii
Expert 5K+
P: 8,669
Access does not have a Binary Data Type, but what the AS400 did was to convert the 6-Byte Date into a Byte() Array containing 6 (0 - 5) Elements corresponding to each Byte of the Date. A Byte is a single, unsigned 8-Bit (1-Byte) number ranging from 0 to 255. The Byte Data Type in Access is primarily used for containing Binary Data. I'm not sure what the actual values represent, but I will look further into it when I get a chance and see what I can come up with.
Aug 24 '08 #3

Expert Mod 2.5K+
P: 2,545
Hi. The AS400 minicomputer uses EBCDIC character coding, not ASCII, and as a conversion table in this link shows 240-249 (F0 hex - F9) represents the characters 0 to 9. In this instance, for the numbers 0 to 9, it is indeed safe to subtract 240 from the byte array values.

However, if at some stage you wish to convert other characters you would have to use different constants for different character groups, which you will find from the EBCDIC coding table. This non-contiguous nature arises from the history of development of EBCDIC way back to punch-card days, as EBCDIC characters were represented by byte grouping of individual 4-bit binary-coded-decimal values. The AS400 is relatively modern, introduced in 1988 and sold until quite recently, so I guess the use of EBCDIC for character storage must relate to its developmental history from mainframe architectures dating back to the sixties.

-Stewart
Aug 24 '08 #4

ADezii
Expert 5K+
P: 8,669
Hi. The AS400 minicomputer uses EBCDIC character coding, not ASCII, and as a conversion table in this link shows 240-249 (F0 hex - F9) represents the characters 0 to 9. In this instance, for the numbers 0 to 9, it is indeed safe to subtract 240 from the byte array values.

However, if at some stage you wish to convert other characters you would have to use different constants for different character groups, which you will find from the EBCDIC coding table. This non-contiguous nature arises from the history of development of EBCDIC way back to punch-card days, as EBCDIC characters were represented by byte grouping of individual 4-bit binary-coded-decimal values. The AS400 is relatively modern, introduced in 1988 and sold until quite recently, so I guess the use of EBCDIC for character storage must relate to its developmental history from mainframe architectures dating back to the sixties.

-Stewart
You are showing your age, Stewart! (LOL).
Aug 24 '08 #5

ADezii
Expert 5K+
P: 8,669
Thanks to Stewart, the mystery is solved! If you are interested in converting the Array of Byte Values to a True Date in Access, then this code should do the trick:
Expand|Select|Wrap|Line Numbers
  1. Dim Value(5) As Byte
  2. Dim dteConvertedDate As Date
  3.  
  4. Value(0) = 240
  5. Value(1) = 248
  6. Value(2) = 240
  7. Value(3) = 248
  8. Value(4) = 242
  9. Value(5) = 242
  10.  
  11. 'Pass the Array of Bytes to the Function
  12. dteConvertedDate = fConvertValueToDate(Value())
  13.  
  14. Debug.Print "Is it a Date: " & IIf(IsDate(dteConvertedDate), "Yes it is!", "Not a Chance")
  15. Debug.Print "Month: " & Month(dteConvertedDate)
  16. Debug.Print "Day: " & Day(dteConvertedDate)
  17. Debug.Print "Year: " & Year(dteConvertedDate)
  18. Debug.Print Format$(dteConvertedDate, "mmmm dd, yyyy")
Expand|Select|Wrap|Line Numbers
  1. Private Function fConvertValueToDate(bytToConvert() As Byte) As Date
  2. 'Function accepts an Array of Bytes and generates a Date from them
  3. 'Valid only in the EBCDIC Coding Syetem and the yymmdd Format for the Date
  4. Dim dteNewDate As Date
  5. Dim intCounter As Integer
  6.  
  7. For intCounter = LBound(bytToConvert) To UBound(bytToConvert)
  8.   varDate = varDate & (bytToConvert(intCounter)) - 240
  9. Next
  10.  
  11. dteNewDate = CDate(Mid(varDate, 3, 2) & "/" & Right(varDate, 2) & "/" & _
  12.                                               Left(varDate, 2))
  13. fConvertValueToDate = dteNewDate
  14. End Function
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. Is it a Date: Yes it is!
  2. Month: 8
  3. Day: 22
  4. Year: 2008
  5. August 22, 2008
Aug 24 '08 #6

ADezii
Expert 5K+
P: 8,669
Thanks to Stewart, the mystery is solved! If you are interested in converting the Array of Byte Values to a True Date in Access, then this code should do the trick:
Expand|Select|Wrap|Line Numbers
  1. Dim Value(5) As Byte
  2. Dim dteConvertedDate As Date
  3.  
  4. Value(0) = 240
  5. Value(1) = 248
  6. Value(2) = 240
  7. Value(3) = 248
  8. Value(4) = 242
  9. Value(5) = 242
  10.  
  11. 'Pass the Array of Bytes to the Function
  12. dteConvertedDate = fConvertValueToDate(Value())
  13.  
  14. Debug.Print "Is it a Date: " & IIf(IsDate(dteConvertedDate), "Yes it is!", "Not a Chance")
  15. Debug.Print "Month: " & Month(dteConvertedDate)
  16. Debug.Print "Day: " & Day(dteConvertedDate)
  17. Debug.Print "Year: " & Year(dteConvertedDate)
  18. Debug.Print Format$(dteConvertedDate, "mmmm dd, yyyy")
Expand|Select|Wrap|Line Numbers
  1. Private Function fConvertValueToDate(bytToConvert() As Byte) As Date
  2. 'Function accepts an Array of Bytes and generates a Date from them
  3. 'Valid only in the EBCDIC Coding Syetem and the yymmdd Format for the Date
  4. Dim dteNewDate As Date
  5. Dim intCounter As Integer
  6.  
  7. For intCounter = LBound(bytToConvert) To UBound(bytToConvert)
  8.   varDate = varDate & (bytToConvert(intCounter)) - 240
  9. Next
  10.  
  11. dteNewDate = CDate(Mid(varDate, 3, 2) & "/" & Right(varDate, 2) & "/" & _
  12.                                               Left(varDate, 2))
  13. fConvertValueToDate = dteNewDate
  14. End Function
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. Is it a Date: Yes it is!
  2. Month: 8
  3. Day: 22
  4. Year: 2008
  5. August 22, 2008
Aug 24 '08 #7

P: 25
This is all excellent information! Thank you very much for your help!

Gregg
Aug 28 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.