如何将十六进制转换为varchar（datetime）？ [英] how to cast the hexadecimal to varchar(datetime)?

问题描述

我的datetime导出是CAST（0x0000987C00000000 AS DateTime），但是当我想把它恢复到datetime.It是一个NULL值。如何才能再次获得日期时间。

解决方案

看起来像SQL Server datetime 格式。内部存储为2个整数，前4个字节为自1900年1月1日以来的天数，第二个是自午夜以来的滴答数（每个刻度为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 
  / pre> 
 
 
（感谢Ted Hopp为解决方案分解二进制数据）
 
I have the datetime exporting is  "CAST(0x0000987C00000000 AS DateTime)" but when I want to get it back into datetime.It is a NULL value. how can i get it to datetime again.
解决方案
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)

                        
这篇关于如何将十六进制转换为varchar（datetime）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！