mysql datetime to varchar_mysql – 如何将十六进制转换为varchar(datetime)？

这看起来像SQL Server日期时间格式.在内部,它存储为2个整数,前4个字节是自1900年1月1日以来的天数,第2个字节是自午夜以来的刻度数(每个刻度为1/300秒).

如果您需要在MySQL中使用它,您可以这样做

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

返回

converted_datetime

--------------------------

2006-11-17 00:00:00

2011-02-09 18:52:34.286667

(感谢Ted Hopp在分割二进制数据时的the solution)