DateTime To Hex

[Dananjaya]

Dear All

I have an application (Windows Based) developed with Sybase DBMS. Applications saves the datetime in hexadecimal format in the database. I just need to undestand how would it be generated thru application (Datetime to Hex conversion).


Below are few samples;
Sample 1:
Real Date&Time : 2013-01-06 22:38:25.000
Stored Hex Val : 0xa1c4e950

Sample 2:
Real Date&Time : 2013-01-06 22:25:07.000
Stored Hex Val : 0x83c1e950

Sample 3:
Real Date&Time : 2013-01-06 22:22:02.000
Stored Hex Val : 0xcac0e950

Can anybody help me to undestand how would be the conversion is. Data type is binary(4) for the Hexa column in database. It's urgent and early suggestions are appriciated.

[Hack]

Are you doing this with Visual Basic 2010 or Sybase itself?

[Joacim Andersson]

This looks like it's a regular Unix time stamp, with big endian. Since it's big-endian the date A1C4E950 is more easily read as 50E9C4A1 (putting the last byte first) which is 1357497505 in decimal. Counting the number of seconds from 1970-01-01 00:00h GMT gives the result: 2013-01-06 18:38:25 GMT which in Abu Dhabi would be 2013-01-06 22:38:25.
Here's a website that can help you with the conversion.

On a side note, this is not really a VB question is it? However I don't really know if it's a Database question either so I've moved this thread to the General Developer forum.

[Dananjaya]

Thank you Joacim. This is the answer I was searching for since very long time. By looking at this conversions, my next question is, is there any internal procedure available in VB 2010 to get the results directly or should it be calculated via manual functions.

What so ever, I'm so happy to know the theoretical matter about this. Please let me know is it possible to do the conversion in VB2010 with internal function or something.

[Joacim Andersson]

Since the hex string is in big endian you need to swap the order of the hex pairs before you can convert it into an integer. You can then calculate the date and time, please note that this would result in the UTC time, you need to add the time difference as well. Something like this:

  Public Function ConvertToDateTime(hex As String) As DateTime
    'Strip of 0x if present
    If hex.Substring(0, 2) = "0x" Then
      hex = hex.Substring(2)
    End If
    'First make sure we have 8 hex digits
    Do While hex.Length < 8
      hex = "0" & hex
    Loop
    'Now since it's big endian we have to reverse the pairs
    hex = hex.Substring(6, 2) & hex.Substring(4, 2) & hex.Substring(2, 2) & hex.Substring(0, 2)
    'Now convert it into a integer number
    Dim value As Integer = Convert.ToInt32(hex, 16)
    'Now calculate the time from Jan 1st, 1970
    Return New DateTime(New DateTime(1970, 1, 1).AddSeconds(value).Ticks, DateTimeKind.Utc).ToLocalTime
  End Function

[Dananjaya]

Thank you very much sir,

Actually, I was wondering if you could help me to do the conversion in other way around. ( Decimal -> Hex ) because I need to convert the current datetime to hex and write to the DB.
Sorry to trouble you a lot. Is there any function to convert to Hex.

Thank you again.