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)
精彩评论