Binary to Decimal conversion

[GreggaR0und]

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

[Dököll]

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!

[ADezii]

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.

[Stewart Ross]

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

[ADezii]

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).

[ADezii]

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:

Dim Value(5) As Byte
Dim dteConvertedDate As Date
 
Value(0) = 240
Value(1) = 248
Value(2) = 240
Value(3) = 248
Value(4) = 242
Value(5) = 242
 
'Pass the Array of Bytes to the Function
dteConvertedDate = fConvertValueToDate(Value())
 
Debug.Print "Is it a Date: " & IIf(IsDate(dteConvertedDate), "Yes it is!", "Not a Chance")
Debug.Print "Month: " & Month(dteConvertedDate)
Debug.Print "Day: " & Day(dteConvertedDate)
Debug.Print "Year: " & Year(dteConvertedDate)
Debug.Print Format$(dteConvertedDate, "mmmm dd, yyyy")

Private Function fConvertValueToDate(bytToConvert() As Byte) As Date
'Function accepts an Array of Bytes and generates a Date from them
'Valid only in the EBCDIC Coding Syetem and the yymmdd Format for the Date
Dim dteNewDate As Date
Dim intCounter As Integer
 
For intCounter = LBound(bytToConvert) To UBound(bytToConvert)
  varDate = varDate & (bytToConvert(intCounter)) - 240
Next
 
dteNewDate = CDate(Mid(varDate, 3, 2) & "/" & Right(varDate, 2) & "/" & _
                                              Left(varDate, 2))
fConvertValueToDate = dteNewDate
End Function

OUTPUT:

Is it a Date: Yes it is!
Month: 8
Day: 22
Year: 2008
August 22, 2008

[ADezii]

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:

Dim Value(5) As Byte
Dim dteConvertedDate As Date
 
Value(0) = 240
Value(1) = 248
Value(2) = 240
Value(3) = 248
Value(4) = 242
Value(5) = 242
 
'Pass the Array of Bytes to the Function
dteConvertedDate = fConvertValueToDate(Value())
 
Debug.Print "Is it a Date: " & IIf(IsDate(dteConvertedDate), "Yes it is!", "Not a Chance")
Debug.Print "Month: " & Month(dteConvertedDate)
Debug.Print "Day: " & Day(dteConvertedDate)
Debug.Print "Year: " & Year(dteConvertedDate)
Debug.Print Format$(dteConvertedDate, "mmmm dd, yyyy")

Private Function fConvertValueToDate(bytToConvert() As Byte) As Date
'Function accepts an Array of Bytes and generates a Date from them
'Valid only in the EBCDIC Coding Syetem and the yymmdd Format for the Date
Dim dteNewDate As Date
Dim intCounter As Integer
 
For intCounter = LBound(bytToConvert) To UBound(bytToConvert)
  varDate = varDate & (bytToConvert(intCounter)) - 240
Next
 
dteNewDate = CDate(Mid(varDate, 3, 2) & "/" & Right(varDate, 2) & "/" & _
                                              Left(varDate, 2))
fConvertValueToDate = dteNewDate
End Function

OUTPUT:

Is it a Date: Yes it is!
Month: 8
Day: 22
Year: 2008
August 22, 2008

[GreggaR0und]

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

Gregg