问题描述
我的日期时间导出是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)
这篇关于如何将十六进制转换为 varchar(datetime)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！