如何将十六进制转换为 varchar(datetime)?

2021-11-20 00:00:00 postgresql datetime sql mysql sql-server

我的日期时间导出是CAST(0x0000987C00000000 AS DateTime)"但是当我想把它恢复到日期时间时.它是一个空值.我怎样才能让它再次到日期时间.

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.

推荐答案

这看起来像 SQL Server datetime 格式.这在内部存储为 2 个整数，前 4 个字节是自 1900 年 1 月 1 日以来的天数，第二个字节是自午夜以来的滴答数(每个滴答声为 1/300 秒).

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).

如果你需要在 MySQL 中使用它，你可以这样做

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

退货

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

(感谢 Ted Hopp 的解决方案拆分二进制数据)

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

相关文章