如何使用存储过程将二进制数据类型转换为datetime？ [英] How to convert binary datatype to datetime using stored procedure?

问题描述

我想使用存储过程和参数将二进制数据类型转换为datetime



i创建以下存储过程但它没有返回正确的值

i want to covert binary datatype to datetime using stored procedure and parameters

i create the following stored procedure but it dosen''t return correct value

create proc Binary_to_DateTime
(
@mydate_bin binary
)
as begin
SELECT CAST(@mydate_bin AS DATETIME)
end

使用此binnary值执行此proc时（0x0000000000000000000000000000000000000000000000008C3000000000 ）



exec Binary_to_DateTime 0x0000000000000000000000000000000000000000000000008C3000000000



它返回1900-01-01 00：00：00.000 这是不正确的



正确值是1998-04-05 00：00：00.000



如何让它返回正确的值？



谢谢

when execute this proc using this binnary value (0x0000000000000000000000000000000000000000000000008C3000000000)

exec Binary_to_DateTime 0x0000000000000000000000000000000000000000000000008C3000000000

it returns 1900-01-01 00:00:00.000 which is not correct

the correct value is 1998-04-05 00:00:00.000

How to make it return the correct value?

Thanks

推荐答案

日期时间值使用了多少字节的存储空间 - 从内存中我认为它''通常是8？你为演员提供了多少字节的二进制数据 - 看起来大约是30 - 我不能可靠地计算很多零:-)你得到的返回值对我来说看起来像一个大的零[即计算日期/时间的基点] - 所以也许演员只看前8个字节（？big-endian / little-endian问题？）。如果你的二进制数据大小合适，你可能得到你想要的答案。值得一试？

How many bytes of storage does a datetime value use - from memory I think it''s typically 8? How many bytes of binary data are you feeding to the cast - looks like about 30 - I can''t count that many zeroes reliably :-) The return value you are getting looks like a big ZERO to me [i.e. the base point for counting dates / times] - so maybe the cast is just looking at the first 8 bytes (? big-endian / little-endian issues ?). If your binary data was the right size you might get the answer you want. Worth a try?

尝试二进制声明的参数：

Try an argument to your binary declaration:

alter proc Binary_to_DateTime
(
@mydate_bin binary(30))
as begin
SELECT @mydate_bin
SELECT CAST(@mydate_bin AS DATETIME)
end

根据 MSDN文档：

It should be binary(8) according to the MSDN documentation:

Values with the datetime data type are stored internally by the SQL Server 2005 Database Engine as two 4-byte integers. The first 4 bytes store the number of days before or after the base date: January 1, 1900. The base date is the system reference date. The other 4 bytes store the time of day represented as the number of 1/300-second units after midnight.

即此次往返：

i.e. this round-trips:

select cast(convert(datetime, '2013-12-01 1:23 PM') as binary(8))
select cast(0x0000A14400DC8CF0 as datetime)

这篇关于如何使用存储过程将二进制数据类型转换为datetime？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！