datetime类型分析解决方案

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 datetimeSET @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的处理方式可能不同）。
记得当年做毕业设计的时候详细了解了一下，今天看到楼主的帖子，仿佛又回到了那个时候。
泪如泉涌中。。。。。。

[解决办法]
sqlserver 2008 支持7位精度了
[解决办法]
关于datetime常量，请看看以下测试语句

SQL code

declare @t datetimeset @t = getdate()declare @i intset @i = datediff(day,'1900-1-1',@t)declare @n numeric(38,30)set @n = datediff(ms,convert(varchar(10),@t,120),@t)/86400000.0 + @ideclare @s varchar(200)set @s = 'declare @t datetimeset @t = ' + cast(@n as varchar(100)) + 'print convert(varchar(23),@t,121)'print @sprint convert(varchar(23),@t,121)exec(@s) 

 

查看更多 下一篇