揭密SQL Server DATETIME数据类型

看完这篇文章的第一感觉是，虽然对于日期类型数据使用得很算顺利，不过作者 提到的一些东西还真不知道。有时候在应用上，不觉得比老外差到那里去。但是， 老外的一个优良习惯细扣概念并进行实证检验；而我们的习惯是概念是概念，应用 是应用。到最后会发现其实有些很基础的东西，是不知其所以然的。

---

原文： Demystifying the SQL Server DATETIME Datatype
来源： SQL-Server-Performance.com
作者：Frank Kalis

When you follow online communities dedicated to SQL Server with open eyes, you certainly notice......

---

你和发现网上很多SQL Server的问题是关于DATETIME数据类型的，这似乎说明熟练使用DATETIME并不容易。

奇怪的是，我却一直相信使用DATETIME是不难的事。DATETIME并非复杂的数据类型，也没有深奥的日期算法。唯一需要 理解的是为了安全的处理临时数据，DATETIME数据类型的一些基本概念。本文的目的就是帮助读者理解这些SQL Server有趣 的地方，以及弄清楚DATETIME数据类型的一些真相。

本文我都会使用ISO日期格式 yyyymmdd。这是一种安全的日期格式，即无论你的电脑如何设置，该格式都可以运行正常，而且 它也不受SET DATEFORMAT或者SET LANGUAGE设置的影响。即使你不开发国际用户的数据库应用，也最好养成使用安全的 日期格式的习惯。SQL Server只有两种日期类型格式编号是安全的，112和116。112是ISO格式，116是ISO8601格式。 在SQL Server联机帮助的CAST和CONVERT主题中可以找到关于这两种日期编号的介绍。你越早养成这些习惯，很多潜在的 问题就越少。

你将注意到，我特意使用了隐示地将CHAR转换成DATETIME。隐示转换有时候不是一种良好的开发习惯，不过根据 SQL Server数据类型中，DATETIME转换的优先级高，我认为转换是安全的。关于这点本文就不多阐述了。

本文先来研究一下DATETIME数据类型的内部表现形式，然后将注意力转移到DATETIME相关的查询上，最后总结一些 注意事项，小技巧和常见问题的解决方法。

所有的代码示例都适用于SQL Server 2000，我相信对于以前的SQL Server版本也应该适用。对于2000以后的版本，我将 检查本文并对不适用的地方作相应的改动。

好了，让我们开始吧！

DATETIME类型的数据的可读性

SELECT CAST(GETDATE() AS BINARY(8)) AS WhatIsReallyStored WhatIsReallyStored ------------------ 0x00009620016CE3A8 (1 row(s) affected)

我必须承认，我对16进制数并不那么精通，所以我想我无法直接告诉你这串数字表示的是 2005-03-23 22:08:31.280. ;-)

SQL Server联机帮助(SQL Server Books Online :BOL)的解释是 "DATETIME数据类型的值储存在2个4byte长度的整数中。"

说明就只有这些。

注意，BOL并没有说DATETIEM类型的值是保存在某种特定的日期或者时间格式中，而这些格式是依赖于 语言和计算机设置的! 而仅仅是说储存在2个整型数值中。这并非100％的描述清楚了。其实是这样的， 该值的确是储存在2个4byte长度的整型中，但是被打包成了BINARY(8)。前4个字节保存和19000101这个日期的 差值，我们知道SQL Server的基准日期是19000101。

后4个字节保存时间数据，该数据是以午夜开始的累积毫秒数。

这些也能在BOL中找到相关的介绍。下面我们用一个简单的脚本来检验。

DECLARE @300 BINARY(8) SET @300 = 0x00000000 + CAST(300 AS BINARY(4))

在讨论运行结果前，我们先来看一下该脚本干什么用。首先定义了一个SQL Server DATETIME内部数据类型。 然后我们将前4个byte(即日期部分)赋值为0x00000000。这意味着我们将它设置为基准日期(=19000101)。 然后在日期部分后连接一个时间部分。时间部分赋值为300，根据BOL，我们知道，这表示300毫秒，即 1/3秒。运行脚本

SELECT @300 , CAST(@300 AS DATETIME) ------------------ ------------------------------------------------------ 0x000000000000012C     1900-01-01 00:00:01.000 (1 row(s) affected)

奇怪，我们的结果显示是1秒，而不是300毫秒。反过来推理，赋值300表示1秒，也就是说它并不是 表示300毫秒，而是300/300秒，即1秒。

实际上，SQL Server的确储存从午夜开始的时钟。每个时钟单位是3.33毫秒。这也就是为什么DATETIME 数据类型最小精确度是1/300。在BOL关于也是如此介绍的。