Converting hexadecimal dates and times

[ddrake]

Hi all,
I've got two fields in a database that I'm trying to report off of that have their data formatted as a numeric representation of a hexadecimal number. We'll call field 1 'DATE' and field 2 'TIME'. The date format is set to a varchar datatype that contains a numeric expression like this '131336971'. Here is an example using the value mentioned earlier

131336971

After conversion to Hexidecimal it becomes;

7D40B0B

Breaking this Hex value down gives us the following Integer values;

7D4 = 2004
0B = 11
0B = 11

This means you would end up with the date "11/11/2004" (DD/MM/CCYY)

The same applies for time;

136780319

After conversion to Hexidecimal it becomes;

8271A1F

Breaking this Hex value down gives us the following Integer values;

8 = 8
27 = 39
1A = 26

This means you would end up with the time "8:39:26" (HH:MM:SS).

What I'd like to do is take these two fields and convert them in Crystal to usable formats.

Any suggestions would be most appreciated! Thanks!!

SQL 2K, CR 9, ARS 6.0

 

[synapsevampire]

You can locate or write a UDF (DLL made available within Crystal).

Since you're using SQL Server, why not expose a View and convert them within?

Another alternative is to use SQL Expressions within Crystal to convert them using a CAST or whatever it is.

-k