日期时间类型的数据存储方式和可用值范围、相关的计算、比较、显示(转换为指定格式的字符串)都比较复杂,还涉及一组日期时间函数。常看到有新手困扰于相关问题,特整理下帖。
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的处理方式可能不同)。
记得当年做毕业设计的时候详细了解了一下,今天看到楼主的帖子,仿佛又回到了那个时候。
泪如泉涌中。。。。。。