SQL Server 日期和时间的内部存储过程
在SQLServer的内部存储中，日期和时间不是以字符串的形式存储的，而是使用整数来存储的。使用特定的格式来区分日期部分和时间部分的偏移量，并通过基准日期和基准时间来还原真实的数据。
一，DateTime的内部存储
SQLServer存储引擎把DateTime类型存储为2个int32类型，共8个字节，第一个int32整数（前4个字节）存储的是日期相对于基准日期（1900-01-01）的偏移量。基准日期是1900-01-01，当前4字节为0时，表示的日期是1900年1月1日。第二个int32整数（后4个字节）存储的是午夜（00:00:00.000）之后的时钟滴答数，每个滴答为1⁄300秒，精确度为3.33毫秒（0.00333秒，3.33ms），因此，DateTime能够表示的时间，可能会存在一个滴答的时间误差。
DateTime的内部存储格式，用十六进制表示是：DDDDTTTT
- DDDD：占用2个字节，表示对基准日期的偏移量
- TTTT：占用两个字节，表示对午夜之后的始终滴答数
举个例子，对于如下的日期和时间，把DateTime类型转换为大小为8个字节的16进制，每两个数字对应1个字节：
declare@dtdatetime='2015-05-0710:05:23.187' selectconvert(varbinary(8),@dt)asdate_time_binary --output0x0000A49100A6463C
1，拆分出date和time
把时间的二进制格式中的字节拆分成两部分：前4个字节表示date，后4个字节表示time，得出的结果如下：
declare@dtdatetime='2015-05-0710:05:23.187' selectsubstring(convert(varbinary(8),@dt),1,4)asdate_binary, cast(substring(convert(varbinary(8),@dt),1,4)asint)asdate_int, substring(convert(varbinary(8),@dt),5,4)astime_binary, cast(substring(convert(varbinary(8),@dt),5,4)asint)astime_int;
2，通过偏移量还原日期和时间
通过基准时间和偏移量，把整数还原为原始的日期和时间：
declare@Timetime='00:00:00.000' declare@Datedate='1900-01-01' selectdateadd(day,42129,@Date)asoriginl_date ,dateadd(ms,10896956*10/3,@Time)asoriginal_time
二，DateTime2的内部存储
DateTime2(n)数据类型存储日期和时间，它是DateTime的升级版本，由于小数秒n的精度可以自主设置，其存储大小（StorageSize）不固定，DateTime2(n)占用的存储空间和小数秒的精度之间的关系是：
- DateTime2(n)内部存储的第一个字节存储精度n，后续的字节用于存储日期和时间的值。
- 当小数秒的精度n<3时，总的存储空间是1B（精度）+6B（数据）；
- 当小数秒的精度n是3-4时，总的存储空间是1B（精度）+7B（数据）；
- 当小数秒的精度n是5-7时，总的存储空间是1B（精度）+8B（数据），最大的小数秒精度是7，默认值是7；
1，二进制逆序
在探索DateTime2(n)的内部存储之前，先了解一下字节存储的“小端”格式和“大端”格式：
- 大端格式：是指数据的低位保存在内存的高地址中，而数据的高位，保存在内存的低地址中；
- 小端格式：是指数据的低位保存在内存的低地址中，而数据的高位保存在内存的高地址中。
举个例子，假如内存地址左边是地位，右边是高位，对于数字275，使用两个字节来存储：
- 如果采用大端格式：字节序列是0x0113
- 如果采用小端格式：字节序列是0x1301
DateTime2(n)的内部存储格式使用的是小端格式，这种格式适合CPU的运算。
2，DateTime2的存储格式
DateTime2(n)的内部存储格式是：
- 第一字节存储的精度n，
- 后三个字节记录从基准日期0001-01-01之后的多少天，采用小端格式。
- 中间余下的字节记录子夜之后经过的时间单位间隔（timeunitinterval，TUI）的数量，采用小端格式。
TUI是由精度来控制的，每一个TUI是10的n次方之一秒，也就是：
- 对于DateTime2(7)，TUI是100ns；
- 对于DateTime2(6)，TUI是1微秒（=1000ns）；
- 对于DateTime2(5)，TUI是10微秒；
- 对于DateTime2(4)，TUI是100微秒；
- 对于DateTime2(3)，TUI是1ms（1毫秒=1000微秒）；
为了便于运算，把DateTime2(n)的字节流逆序排列：前3个字节表示的是天数，最后一个字节表示的是精度，中间余下的字节表示的TUI的数量。例如，对于DateTime2(7)按照字节流逆序处理之后，存储空间是9个字节：前三个字节是存储的从基准日期0001-01-01之后的多少天，最后一位是精度n，中间的5个字节表示从子夜开始有多少个TUI。
2，把DateTime2转换为二进制存储
把DateTime2转换为二进制存储，并作逆序处理，DateTime2(3)的精度为3，存储空间是8个字节，后三个字节记录从基准日期0001-01-01之后的多少天，前3个字节表示从子夜开始有多少个TUI。
declare@dtdatetime2(3)='2015-05-0710:05:23.187' declare@dt_bivarbinary(max)=convert(varbinary(max),@dt) select@dt_biasdate_time_binary ,convert(varbinary(max),reverse(@dt_bi))asreverse_binary
把二进制值拆分成DateTime2(3)的各个组成成分：
declare@dtdatetime2(3)='2015-05-0710:05:23.187' declare@dt_bivarbinary(max)=convert(varbinary(max),@dt) declare@dt_bi_littleEndvarbinary(max) select@dt_bi_littleEnd=convert(varbinary(max),reverse(@dt_bi)) selectsubstring(convert(varbinary(8),@dt_bi_littleEnd),1,3)asdate_binary, cast(substring(convert(varbinary(8),@dt_bi_littleEnd),1,3)asint)asdate_int, substring(convert(varbinary(8),@dt_bi_littleEnd),4,4)astime_binary, cast(substring(convert(varbinary(8),@dt_bi_littleEnd),4,4)asint)astime_int, substring(convert(varbinary(8),@dt_bi_littleEnd),8,1)asprecision_binary, cast(substring(convert(varbinary(8),@dt_bi_littleEnd),8,1)asint)asprecision_int;
3，利用偏移量和基准还原原始值
有了偏移量，就可以在基准日期和时间之上加上偏移量来获得原始值：
declare@Timetime='00:00:00.000' declare@Datedate='0001-01-01' selectdateadd(day,735724,@Date)asoriginl_date ,dateadd(ms,36323187,@Time)asoriginal_time
参考文档：
WhatistheSQLServer2008DateTime2InternalStructure?
HowtoGetSQLServerDatesandTimesHorriblyWrong
总结
以上所述是小编给大家介绍的SQLServer日期和时间的内部存储，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持！
如果你觉得本文对你有帮助，欢迎转载，烦请注明出处，谢谢！
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。