Convert Hexadecimal to Datetime

[CingularChris]

I have a date time stamp from a dbIV database (from an app that I need to report on) that is in hexadecimal format. I need to convert that to datetime. I know very little about hexadecimal other than what it is and roughly how it works (0-5,a-f or something like that.) Thanks!

 

[SgtJarrow]

Hexadecimal is a number system based on 16 (0-9, a=10, b=11, c=12, d=13, e=14 and f=15). So simply converting from hex to decimal is not so difficult. Do you know how your "timestamp" is made up??? Is it the same as a windows datetime base and just converted to hexidecimal - in other words a windows datetime can be thought of as a number, with the whole number being days from a certain point in time and the decimal portion being seconds for that day. Or is it some setup like mm/dd/yyy an each part is converted to hex? That will make a difference in how to extract the data you seek.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[CingularChris]

A sample of the hex datetime stamp is '456985c7'. A non-corallating example of the datetime format stamp is 'Fri Oct 13 09:07:29 2006'.

 

[Golom]

As mstrmage1768 says, it's fairly simple to convert between decimal and HEX.

Dim HexNumber As String
Dim DecimalNumber As Long

HexNumber = HEX(2234) [COLOR=black cyan]' <-- 8BA[/color]

DecimalNumber = &hA5  [COLOR=black cyan]' <-- 165[/color]

The real issue is that Access dates are internally doubles with the date before the decimal place and the time after it. They are based on Midnight, December 30, 1899 as 0.0.

What is the base value for the dates that you are converting from HEX?

 

[CingularChris]

I'm not sure. It's a kludgy proprietary app db that's based on dbIV.

 

[SgtJarrow]

OK...you may want to look at :

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10261

http://www.alegsa.com.ar/N/i03/Conv...ifferent behaviors on different databases.php

http://www.sqlteam.com/item.asp?ItemID=1410

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=470226&SiteID=1

unless you specifically know what part of each of the characters in the "stamp" relate to in terms of datetime. If it is truly a timestamp from a database system, you may be out of luck.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[Golom]

I thought it might be the number of seconds since some base date and time but, if it was, that base date and time would be

[red]11/17/1969 8:50:18 PM[/red]​

which seems like a very odd date and time to base things on.

mstrmage1768 is probably right. Without some information on how those HEX values are parsed to produce a date/time stamp, you're probably out of luck.

 

[PHV]

Perhaps an Unix timestamp ?
? Format(#01/01/1970#+(&h456985c7/3600/24),"yyyy-mm-dd hh:nn:ss")
2006-11-26 12:17:11

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886