how to cast the hexadecimal to varchar(datetime)?

Solution 1:

That looks like the SQL Server datetime format. Internally this is stored as 2 integers with the first 4 bytes being the days since 1st jan 1900 and the 2nd being the number of ticks since midnight (each tick being 1/300 of a second).

If you need to use this in MySQL you could do

SELECT 
      CAST(
          '1900-01-01 00:00:00' + 
          INTERVAL CAST(CONV(substr(HEX(BinaryData),1,8), 16, 10)  AS SIGNED) DAY +
          INTERVAL CAST(CONV(substr(HEX(BinaryData),9,8), 16, 10)  AS SIGNED)* 10000/3 MICROSECOND
      AS DATETIME) AS converted_datetime
FROM
(
SELECT 0x0000987C00000000 AS BinaryData
UNION ALL
SELECT 0x00009E85013711EE AS BinaryData
) d

Returns

converted_datetime
--------------------------
2006-11-17 00:00:00
2011-02-09 18:52:34.286667

(Thanks to Ted Hopp for the solution in splitting the binary data)

Solution 2:

Not really adding anything that hasn't been stated but I used this to create a MySql function from the above code. I can then use a RegEx find and replace (in Notepad++) to replace the CAST(0xblahblahblah AS DATETIME) with sp_ConvertSQLServerDate(0xblahblahblah).

create function sp_ConvertSQLServerDate(dttm binary(16))
returns datetime
return CAST(
      '1900-01-01 00:00:00' + 
      INTERVAL CAST(CONV(substr(HEX(dttm),1,8), 16, 10)  AS SIGNED) DAY +
      INTERVAL CAST(CONV(substr(HEX(dttm),9,8), 16, 10)  AS SIGNED)* 10000/3 MICROSECOND
AS DATETIME);