SQL SERVER2005数据类型

1、sql server2005支持两种与日期和时间相关的数据类型：Datetime和SmallDatetime。

2、时间类型的存储： 

Microsoft SQL Server 2005 Database Engine 用两个 4 字节的整数内部存储 datetime 数据类型的值。 第一个 4 字节存储“基础日期”（即 1900 年 1 月 1 日）之前或之后的天数。基础日期是系统参照日期。另外一个 4 字节存储天的时间（以午夜后经过的毫秒数表示）。

smalldatetime 数据类型存储天的日期和时间，但精确度低于 datetime。数据库引擎 将 smalldatetime 值存储为两个 2 字节的整数。第一个 2 字节存储 1900 年 1 月 1 日后的天数。另外一个 2 字节存储午夜后经过的分钟数。

3、取整问题：

当精确的显示到毫秒时，Datetime类型的毫秒部分匹配[0-9][0-9][0/3/7]模式

Smalldatetime会精确到分钟数：将等于或小于 29.998 秒的 smalldatetime 值向下舍入到最接近的分钟数；将等于或大于 29.999 秒的值向上舍入到最接近的分钟数。

<1>DateTime和字符串间的转换并不总是十分精确的。他们之间的转换遵循上述规则。例如：把‘20060923 03:23:47.001’转换为Datetime，它将比字符串所表示的时间早1毫秒；如果把同样的字符串转换到Smalldatetime，则要比字符串所表示的时间晚13秒。同样，从时间到字符串的转换也并不总是精确的。当把包含毫秒的Datetime值转换为字符串格式时，将被舍入到最近的毫秒。否则，不包含在目标格式的时间字段将被截断。

<2>你指定的时间类型值与存储在数据库中的值也会有差异。规则同上。

<3>如果要查找DateTime列（称为dt）处于某一时间内的行时，不能使用下面的筛选器：where dt between '20060211 00:00:00.000'and'20060211 23:59:59.999' ,这是因为'20060211 23:59:59.999'会转化成'20060212 00:00:00.000'。应该用下面的筛选器：where dt>='20060211'and dt<'20060212'

4、字面值：

<1>默认情况下：如果字符串出现在期望Datetime的上下文中，SQL SERVER将会根据当前会话的语言设置进行转换。会话的语言是由登录的默认语言决定的。可以用set language重新设置会话语言。

<2>用 set dateformat：SET DATEFORMAT { format | @format_var }设置用于输入 datetime 或 smalldatetime 数据的日期部分（月/日/年）的顺序。

format | @format_var日期部分的顺序。可以是 Unicode，或者是转换为 Unicode 的 DBCS。有效参数包括 mdy、dmy、ymd、ydm、myd 和 dym。美国英语默认值是 mdy。

注意：

该设置仅用在将字符串转换为日期值时的解释中。它不影响日期值的显示。

SET DATEFORMAT 的设置是在执行或运行时设置，而不是在分析时设置。

SET DATEFORMAT 将覆盖 SET LANGUAGE 的隐式日期格式设置。

<3>独立于系统设置和开关的代码：它的日期部分没有分隔符：[yy]yymmdd[hh:mi[:ss][.mmm]],这样比如'20050212','060212','20060211 23:59:59.997'都是合法格式。

DATETIME函数

DateAdd为datetime值添加间隔。例如，表达式dateadd（month，1，'20060702')为2006年7月2日添加一个月，增加一个负值表示减少。

注意：当要添加或减少一个天数时，可以使用+或-运算符及一个整数实现与dateadd函数一样的功能。例如dateadd（day，1，dt）等价于dt+1。

DateDiff计算两个datetime值之间指定的日期部分的差。例如，表达式 datediff（month，'2006725','20060825')计算这两个时间相差的月数。

注意：DateDiff函数不考虑比指定的日期部分更高的粒度级别；它只考虑更低级别的部分。

datepart：从datetime值提取指定的datetim部分，并返回一个整型。

datename：从datetime值提取指定的datetime部分，但返回是以字符串形式返回指定日期部分的名称（没名称时返回数字）。

 5、DateTime相关的查询问题

<1>当在一段时间内查询某些记录的时候，用下面的逻辑进行查询，这样做优化器可以更高效地出理AND逻辑。思路是：若果对于两个重叠的记录，其中一个记录必定是在另一个记录开始时或开始后结束，在另一个记录结束时或结束前开始。例如查询凡是与t1-t2有重叠的记录

select *from test where endtime>=t1 and starttime<=t2;

<2>标识星期数：标识指定日期的星期数，即计算出该日期是星期几。可以用下面这个公式来解决：

  datepart（weekday，dt+@@datefirst-n)

参数：@@datefirst：可以通过它访问会话中有效的dataefirst值。

        n：给datefirst逻辑上设置值

例如：select datepart(weekday,cast('20101006' as datetime)+@@datefirst-1);

显示的结果为3；如果把n的值改为4，则显示的结果为7；

<3>按周分组：有两种思路：第一种：现根据给定的日期（od）算出星期数（wt)，然后用od-wt+1即可算出这一周的开始日期，并用od+7-wt算出这一周的结束日期

                                     第二种：首先给定一个参照日期。然后计算每个需要计算的日期从参照日期开始记过的所有周数（week），然后通过参照日期加上week*7的天数该周开始日期，每周开始日期加上6得到结束日期。