Timestamp String Conversion

[Grant Fritchey]

Hello all,

I've pounded my head against this for several days now and I've finally given
up.

I need to read a Timestamp out of the database and use it to compare to a
previous timestamp in DTS. This should be simple, but it's not.

The code to get a timestamp out of a db as a string is well documented in
sp_hexadecimal. That works great and I am using it to get my comparison value.
I need a vb script function that will take the byte array (that's what binary
values in SQL convert to in VB) and convert it into a string the same way
as sp_hexadecimal does so that I can compare the two.

A sde note, the columns return with a ubound of 7. That's correct. It translates
as an array lenght of eight. However, the column value translates as a lenght
of 4. Only four of the eight bytes are there. What's up with that?.

Someone out there is doing this. Please let me know what the trick is.

Thanks,

Grant

[Eric Litwin]

"Grant Fritchey" <gfritchey@tscentral.com> wrote:
>I need to read a Timestamp out of the database and use it to compare to
a
>previous timestamp in DTS. This should be simple, but it's not.

The following are two function that perform the conversions you need:

' Converts a Binary(8) to a String
Public Function TimeStampToString(Value As Variant) As String
Dim strBuffer As String
Dim i As Integer

strBuffer = "Ox"
For i = 1 to 8
strBuffer = strBuffer & Right$("00" & _
Hex(AscB(MidB(Value, i, 1))), 2)
Next

TimeStampToString = strBuffer
End Function

' Converts a String to a timestamp Binary 8
Public Function StringToTimeStamp(Value As String) As Variant
Dim ByteArray(0 to 7) As Byte
Dim strTemp As String
Dim strHex As String
Dim i As Integer

strHex = "&H"
For i = 0 to 7
strTemp = Mid(Value, (i * 2) + 3, 2)
ByteArray(i) = CByte(strHex & strTemp)
Next

StringToTimeStamp = ByteArray
End Function