datetime类型分析解决方案-MSSQL教程-爱易网页

datetime类型分析解决方案

日期：2014-05-18　浏览次数：20505 次

datetime类型分析
日期时间类型的数据存储方式和可用值范围、相关的计算、比较、显示（转换为指定格式的字符串）都比较复杂，还涉及一组日期时间函数。常看到有新手困扰于相关问题，特整理下帖。

datetime类型

SQLServer中的datetime是8个字节，前4个字节表示从19000101开始的天数，后4个字节表示从00:00:00开始的10/3毫秒间隔的个数（最多精确到3又1/3毫秒）。
smalldatetime是4个字节，前2个字节表示天数，后2个字节表示时间，但日期范围和时间精度都比datetime低很多，详见联机丛书。

把datetime类型转成decimal，则前4个字节转为整数部分（天数），后4个字节转成小数部分（间隔个数比例）。
把datetime类型转成int，相当于对转成的decimal四舍五入取整。
如果需要按指定格式显示，则用CONVERT([n][var]char(x),datetime-type,style)的方式将日期转为字符串。各种可用的style和转换效果详见联机丛书。

相反方向的转换规则相同。

示例：

SQL code


DECLARE @dt datetime
SET @dt = GETDATE()
SELECT @dt,
    CAST(@dt AS binary(8)), --实际物理存储格式
    CAST(@dt AS int),                        --datetime转为int是四舍五入
    CAST(CAST(@dt AS decimal(18,9)) AS int), --decimal转为int是舍去小数
    CAST(@dt AS decimal(18,9)),                        --datetime转为decimal
    CAST(SUBSTRING(CAST(@dt AS binary(8)),1,4) AS int) --分开日期和时间部分，分析datetime转为decimal的规则
        + CAST(SUBSTRING(CAST(@dt AS binary(8)),5,8) AS int) * 1.0 / (24 * 60 * 60 * 300) --一秒中有300个10/3毫秒的间隔

T-SQL不支持datetime类型的常量，通常是以指定格式字符串的方式表示，如果需要指定为datetime类型，则可以用CONVERT或CAST进行显式转换：

SQL code


SELECT sdt,
    dt1 = CAST(sdt AS datetime),  --显式转换
    dt2 = DATEADD(day,0,std)      --隐式转换
FROM (
    SELECT sdt = '' UNION --CAST('' AS datetime) == CAST(0 AS datetime)
    SELECT sdt = '01:02' UNION
    SELECT sdt = '01:02:03:004' UNION
    SELECT sdt = '20100521' UNION
    SELECT sdt = '2010-05-21' UNION
    SELECT sdt = '20100521 01:02' UNION
    SELECT sdt = '20100521 01:02:03' UNION
    SELECT sdt = '20100521 01:02:03.004'
) tmp

以下方式是平台独立（不依赖于区域语言和日期格式设置）最常用方式（专家建议）：
'[yy]yymmdd[ hh:mi[:ss][.mmm]]'
貌似加上“-”分隔符也可以，但是不是完全平台独立没有测试过：
'[yy]yy-mm-dd[ hh:mi[:ss][.mmm]]'

详情参见《SQL Server 2005技术内幕：T-SQL程序设计》1.1节。

日期相关常见问题

1. 如果只想存日期或时间怎么办？

在SQL Server 2008中有date和time两种新增类型。
如果是2008之前的版本，可以用char(8)存储形如'20100826'的日期，用char(10)存储形如'12:10:35'的时间，并且在计算时可以方便地转换为datetime类型。如果想要节省空间，也可以用int存储天数和时间间隔（参考datetime的实现机制），但需要一点特殊的计算。用int存储形如20100826的数值表示日期也是一种办法，与char(8)的方案类似，但要多一步转换。

2. datetime类型要怎么查询？

查询20100826当天的数据

SQL code


--以下查询对dtcolumn使用了函数，将不能使用索引
SELECT * FROM [table] WHERE DATEDIFF(day,dtcolumn,'20100826') = 0
--以下两种方法性能更好
SELECT * FROM [table] WHERE dtcolumn BETWEEN '20100826' AND '20100826 23:59:59' --只精确到秒，23:59:59之后一秒内的数据将无法查出
SELECT * FROM [table] WHERE dtcolumn >= '20100826' AND dtcolumn < '20100827'    --完全精确，但需要把dtcolumn列名写两遍

按天汇总指定时段数据

SQL code


SELECT
    统计周期 = CONVERT(char(10),dtcolumn,120),
    汇总 = COUNT(*) --或其它聚合函数
FROM [table]
WHERE dtcolumn BETWEEN '20090101' AND '20101231 23:59:59'
GROUP BY CONVERT(char(10),dtcolumn,120)
ORDER BY 统计周期
--因为datetime按120转换为字符串的格式是2010-08-26 12:23:14，所以上面的CONVERT目标字符串长度改为4,7,10,13,16,19分别可以按年、月、日、时、分、秒统计。

新的日期时间类型

SQL Server 2008中新引入了date, time, datetime2, datetimeoffset几种类型。目前2008版本使用不多，暂不进行分析了。只要按照数据存储方式和可用值范围、相关的计算、比较、转换规则、支持的日期时间函数这样的顺序理清楚，问题自然迎刃而解。

------解决方案--------------------
不错的基础帖，推荐一下。
------解决方案--------------------
个人经验，datetime类型要注意的是控制显示的格式，以及它和客户端前台开发语言数据类型上的转化（有些语言也设置了专门的时间类型，如Delphi，不过和MSSQL的处理方式可能不同）。
记得当年做毕业设计的时候详细了解了一下

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

datetime类型分析解决方案

相关资料更多>

推荐阅读更多>