T-SQL中的时间表示

 

日常使用SQL时经常为日期的字符串表示发愁，是2006/1/8,1/8/2006,还是2007-1-8。如果是1/8/2006,究竟表示的是2006年１月８日还是２００６年８月１日。这都是问题。直到读了这本《SQL Server2005技术内幕:T-SQL程序设计》(Inside Microsoft SQL server 2005: T-SQL Programming)才豁然开朗。

SQL中的日期表示是和系统的语言设置相关的。你可以在控制面板中打开“区域和语言选项”。在区域选项栏中可以看到不同的语言选项会有不同的数字、货币、时间、短日期和长日期选项。下面是两个例子

中文（中国）：

数字：123,456,789.00

货币：￥123,456,789.00

时间：8:19:42

短日期：2007-10-15

长日期：2007年10月15日

 

英语（美国）：

数字：123,456,789.00

货币：$123,456,789.00

时间：8:22:38 AM

短日期：10/15/2007

长日期：Monday, October 15, 2007

 

西班牙语（阿根廷）：

数字：123.456.789,00

货币：$123,456,789.00

时间：08:25:28 a.m.

短日期：15/10/2007

长日期：Lunes, 15 de Octubre de 2007

 

 

可见不同的设置日期会有不同的表示法。10/11/2007这个日期在英语（美国）中会被解释成2007年10月11日，而在西班牙语（阿根廷）中则是2007年11月10日。如果我们在实际应用中使用这种和语言设置相关的日期，在不同的系统上可能会得到不一致的结果，从而导致混乱和出错。

 

在T-SQL中你可以通过SET LANGUAGE和SET DATEFORMAT来控制日期的格式化，但可能你其它部分的程序在这种语言下不能正常工作，从而引起更大的混乱。

一种好的方法是使用与语言设置无关的日期表示。在T-SQL中有两种与系统设置无关的日期表示方法。一种是"[yy]yymmdd[ hh:mi:ss[.mmm]]"。"20071015", "071015"和"20071015 08:33:05.345"都是这种表示法。DATEFORMAT和LANGUAGE的设置均不影响对这种表示法的解释。另外一种独立于设置的表示法是"yyyy-mm-ddThh:mi:ss[.mmm]","2007-10-15T08:33:05.345"就是这种表示。请注意这种表示中时间部分不能省略。

 

今天又发现了一个问题

DateTime需要8个字节表示，前4个字节用1900年1月1日以后的天数表示日期，后4个字节表示一天中的时间，从午夜开始，以3又1/3毫秒为单位。不能被精确表示的DateTime值被舍入到最近的可以表示的值。如'20071024 22:22:22.998'转换为DateTime将会舍入到最近的百分之三秒。试着运行一下下面的SQL语句，你会有更直观的印象

 

declare @t as datetime

set @t = '20071024 22:22:22.998'

select @t

 

 

我算了一下，一天有86400秒，折算成86,400,000毫秒。而4字节能表示的最大无符号数为4,294,967,295,即使用有符号数来表示也太富裕了。不明白T-SQL为什么设计成这样。

但既然已经这样了，就要去了解这种设计下需要注意的一些细节。

如下面一个filter就不是很好:

 

WHERE dt BETWEEN '20060211 00:00:00.000' and '20060211 23:59:59.999'

 

dt列等于'20060212 00:00:00.000'的行也符合条件。如果dt确定是一个DateTime,而不会变成SmallDateTime,则可以用'20060211 23:59:59.997'，作者给出的一个解决方案是

 

WHERE dt >= '20060211 00:00:00.000' and dt < '20060212 00:00:00.000'

 

这种方案其实也有问题，如果'20060212 00:00:00.000'在内部用'20060211 23:59:59.998'表示，则dt列等于'20060211 23:59:59.999'的列就不符合条件了。