开发者

SQL Server DateTime format from automatic export script

When using the mssql script generation with data the datetime is exported with a cast:

CAST(0x00009E0E0095524F AS DateTime)

Does anyone know what format is used ?

The date in the example开发者_Python百科 is shown as 2010-10-13 09:03:39.783.


Upper 4 bytes = days from 01 Jan 1900, lower 4 = time of day

It's the internal storage of datetime which is 8 bytes as two 4 byte integers, one with whole days, the other as fraction of day.

DECLARE @inttop bigint,  @TheValue bigint
SET @inttop = POWER(CAST(2 AS bigint), 32)
SET @TheValue = CAST(0x00009E0E0095524F AS bigint) 

SELECT
    --days since 01 Jan 1900
    @TheValue / @inttop,
    --fractional time of day
    CAST(@TheValue % @inttop AS float) / @inttop

--and confirm it
SELECT
    DATEADD(DAY, @TheValue / @inttop, 0),
    CAST(CAST((@TheValue % @inttop) AS float) / @inttop AS datetime)
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜