What is the underlying datastructure of datetime values stored in SQL Server (2000 and 2005 if different)? Ie down to the byte representation?
存储在SQL Server(2000和2005,如果不同)中的日期时间值的基础数据结构是什么?即到字节表示?
Presumably the default representation you get when you select a datetime column is a culture specific value / subject to change. That is, some underlying structure that we don't see is getting formatted to YYYY-MM-DD HH:MM:SS.mmm.
大概是您选择日期时间列时获得的默认表示是特定于文化的值/可能会更改。也就是说,我们看不到的一些底层结构被格式化为YYYY-MM-DD HH:MM:SS.mmm。
Reason I ask is that there's a generally held view in my department that it's stored in memory literally as YYYY-MM-DD HH:MM:SS.mmm but I'm sure this isn't the case.
我问的原因是我的部门普遍认为它存储在内存中的字面意思是YYYY-MM-DD HH:MM:SS.mmm但我确信情况并非如此。
1 个解决方案
#1
13
It's stored as an 8 byte field, capable of a range from 1753-01-01 through 9999-12-31, accurate to 0.00333 seconds.
它存储为8字节字段,范围从1753-01-01到9999-12-31,精确到0.00333秒。
The details are supposedly opaque, but most resources (1), (2) that I've found on the web state the following:
据说细节不透明,但我在网上找到的大多数资源(1),(2)都说明如下:
The first 4 bytes store the number of days since SQL Server's epoch (1st Jan 1900) and that the second 4 bytes stores the number of ticks after midnight, where a "tick" is 3.3 milliseconds.
前4个字节存储自SQL Server的纪元(1900年1月1日)以来的天数,后4个字节存储午夜后的刻度数,其中“tick”为3.3毫秒。
The first four bytes are signed (can be positive or negative), which explains why dates earlier than the epoch can be represented.
前四个字节是有符号的(可以是正数或负数),这解释了为什么可以表示早于时期的日期。
#1
13
It's stored as an 8 byte field, capable of a range from 1753-01-01 through 9999-12-31, accurate to 0.00333 seconds.
它存储为8字节字段,范围从1753-01-01到9999-12-31,精确到0.00333秒。
The details are supposedly opaque, but most resources (1), (2) that I've found on the web state the following:
据说细节不透明,但我在网上找到的大多数资源(1),(2)都说明如下:
The first 4 bytes store the number of days since SQL Server's epoch (1st Jan 1900) and that the second 4 bytes stores the number of ticks after midnight, where a "tick" is 3.3 milliseconds.
前4个字节存储自SQL Server的纪元(1900年1月1日)以来的天数,后4个字节存储午夜后的刻度数,其中“tick”为3.3毫秒。
The first four bytes are signed (can be positive or negative), which explains why dates earlier than the epoch can be represented.
前四个字节是有符号的(可以是正数或负数),这解释了为什么可以表示早于时期的日期。