SQL SERVER 2005 中的日期时间类型

SQL SERVER 2005 中的日期时间类型     
一．  数据类型  
MS SQL Server 2005 有 datetime 和 smalldatetime 两种格式的日期时间数据类型。注意：没有单独的日期或时间类型。   
datetime  
数据库内部用两个 4 字节的整数存储 datetime 数据类型的值。第一个 4 字节存储基础日期（即 1900-1-1, base date ）之前或之后的日期。日期范围为 1753-1-1 至 9999-12-31 。当日期为 1900-1-1 时值为 0 ；之前的日期是负数；之后日期是正数。另外一个 4 字节存储以午夜后三又三分之一（ 10/3 ）毫秒数所代表的每天的时间。精确度为百分之三秒（等于 3.33 毫秒或 0.00333 秒）。如下表所示，把值调整到 .000 、 .003 、或 .007 秒的增量。      
查询中，我们常常需要搜索指定日期范围内的数据，比如返回 1998-01-01 当天内的数据，你可能会这样写： 
Date >= '1998-01-01 00:00:00.000' and date <= '1998-01-01 23:59:59.999'  
根据上面的调整规则，其实这句语句的实际搜索范围为： 
date >= '1998-01-01 00:00:00.000' and date <= '1998-01-02 00:00:00.000'  
你会看到这包括了 1998-01-02 的数据，所以最好的正确的搜索语句为： date >= '1998-01-01 00:00:00.000' and date < '1998-01-02 00:00:00.000'  
smalldatetime 
smalldatetime 数据类型存储日期和每天的时间，但精确度低于 datetime 。 SQL Server 将 smalldatetime 的值存储为两个 2 字节的整数。第一个 2 字节存储 1900-1-1 后的天数。另外一个 2 字节存储午夜后的分钟数。日期范围从 1900-1-1 到 2079-6-6 ，时间精确到分钟。 29.998 秒或更低的 smalldatetime 值向下舍入为最接近的分钟， 29.999 秒或更高的 smalldatetime 值向上舍入为最接近的分钟。  
--returns time as 12:35 
SELECT CAST ( '2000-05-08 12:35:29.998' AS smalldatetime ) GO  
--returns time as 12:36 
SELECT CAST ( '2000-05-08 12:35:29.999' AS smalldatetime ) GO  
二．  日期时间的赋值 



 


 
上面说了格式，当然我们是没有办法直接赋整数给日期时间变量的。给这些变量赋值通常是给它指定个一定格式的字符串。 SQL SERVER 会自动将字符串转换成日期格式保存的，注意：数据库中是不会保存数据格式的字符串的。 有两类格式的日期格式的字符串， SQL SERVER 会非常轻易就“认出”的。  
一类是 ISO 时间格式（由 ISO8601 规定）： yyyy-mm-ddT hh:mi:ss[.mmm] ，必须指定每一个元素，只有毫秒是可选的，时间成分以 24 小时格式指定。  
另一类 SQL SERVER 规定的格式：没有分隔符为 [yy]yymmdd[hh:mi[:ss][.mmm]] ；有分隔符为 {[[yyy]y-[m]m-[d]d]|hh:mi[[:ss][.mmm]]} 。后面这种格式中，时间和日期都是可选的（包括时间部分的秒和毫秒部分都是可选的）。如果时间部分没有定义，那么 SQL SERVER 就将子夜（ midnight ）时间作为默认的时间值；如果日期部分没有进行定义，那么 SQL SERVER 就将 1900 年 1 月 1 日作为默认的日期值。  
除了上面的格式，还有很多比较“模糊”的字符串日期时间格式，此时有两种处理方法：或者是使用 DATEFORMAT 规定格式；或者是使用 CONVERT 函数来进行显式转换。  
1. 像“ 01/02/03 ”这个日期格式，不同国家可能解释不同：      
像这种情况，我们若要直接赋值给日期变量，我们就可以使用 SET DATEFORMAT 来设置格式：参数包括 mdy 、 dmy 、 ymd 、 ydm 、 myd 和 dym 。这些参数指出了年月日的顺序。  
2. 还有很多格式，如： dd-mm-yy 、 dd.mm.yy 等等， 若要将这类字符串赋值给日期时间变量，我们只有使用最后的“杀手锏”了——使用 CONVERT 函数来显式转换， CONVERT 的第三个参数对于日期格式和字符串格式定义如下：     
三．日期的输出 
与处理输入数据格式类似，当处理日期时间格式的数据输出时， CONVERT 函数仍然是最常用的 T-SQL 函数。当使用 CONVERT 函数处理日期时间的输出格式时，我们可以使用与处理输入数据时完全相同的风格设置，唯一的区别是：处理输出数据格式时， CONVERT 函数将一个日期时间实例转换为一个字符串，而处理输入数据格式时，则是从一个字符串生成一个日期时间实例。  
四．时间函数 
常用的时间函数如下：  
DATEADD ：可以对时间类型的指定部分进行加减计算。我们常常根据一个时间来构造出另



 


外一个时间，比如下个月的今天 , 本月底等等，我们应该也尽量使用 DATEADD 函数来构造，它可以避免一些闰月、年底、月底之类的错误。对一个日期时间变量直接加减一个整数和使用 DATEADD(DAY, n,@D) 的功能是一样的。  
DATEDIFF ：该函数对两个时间变量对指定部分进行比较计算。此函数不考虑比指定日期部分更高的粒度级别，它只考虑更低级别的部分。对时间的比较应尽量使用本函数。使用 DATEDIFF 和 DATEADD 可以对日期时间变量进行“截尾”的操作（举例见下面的常用查询）。  
DATEPART ：返回日期时间变量的指定部分的值。  
DATENAME ：返回日期时间变量的指定部分的值，和 DATAPART 不同的是本函数返回的是个字符串类型。  
GETDATE() 返回本机器的当前时间。 CURRENT_TIMESTAMP 变量与本函数功能相同。  
GETUTCDATE() 返回本机器的当前 UTC （格林尼治标准）时间。  
DATEADD 、 DATEDIFF 、 DATEPART 和 DATENAME 函数使用到一些共同的参数与缩写如下：        
利用上面的函数，总结一些常用的查询（可以看看是如何进行“截尾”操作的）：  
-- 本月开始，相当于将本月的日期“截尾” 
SELECT DATEADD ( MONTH , DATEDIFF ( MONTH , 0, GETDATE ()), 0)  
-- 本年开始，相当于将本年的月份“截尾” 
SELECT DATEADD ( YEAR , DATEDIFF ( YEAR , 0, GETDATE ()), 0)  
-- 本周一，相当于将本周的日期“截尾” 
SELECT DATEADD ( WEEK , DATEDIFF ( WEEK , 0, GETDATE ()), 0)  
-- 本季度开始，相当于将本季的日期“截尾” 
SELECT DATEADD ( QQ, DATEDIFF ( QQ, 0, GETDATE ()), 0)  
-- 本月结束 
SELECT DATEADD ( MONTH , DATEDIFF ( MONTH , 0, GETDATE ()) + 1, 0) - 1  
-- 本年结束 
SELECT DATEADD ( YEAR , DATEDIFF ( YEAR , 0, GETDATE ()) + 1, 0) - 1  
-- 本周结束 



 


SELECT DATEADD ( WEEK , DATEDIFF ( WEEK , 0, GETDATE ()) + 1, 0) - 1  
-- 本季度结束 
SELECT DATEADD ( QQ, DATEDIFF ( QQ, 0, GETDATE ()) + 1, 0) - 1   
--足年计算法一. 
--使用PUBS数据库中的EMPLOYEE表。（下同） 
SELECT *, DATEDIFF(YEAR, HIRE_DATE, GETDATE()) - CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, HIRE_DATE, GETDATE()), HIRE_DATE) > GETDATE() THEN 1 
                                                      ELSE 0                                                  END FROM EMPLOYEE 
--此法对于闰年的2月29日和平年的2月28日是相等的。  
--足年计算法二. 
SELECT *, DATEDIFF(YEAR, HIRE_DATE, GETDATE()) - CASE WHEN 100 * MONTH(HIRE_DATE) + DAY(HIRE_DATE) > 100 * MONTH(GETDATE()) + DAY(GETDATE())   
THEN 1 
                                                      ELSE 0                                                  END FROM EMPLOYEE --或 SELECT *, DATEDIFF(YEAR, HIRE_DATE, GETDATE()) - CASE WHEN SUBSTRING(CONVERT(VARCHAR(8), HIRE_DATE, 112), 5, 4) > SUBSTRING(CONVERT(VARCHAR(8),   
GETDATE(), 112), 5, 4) THEN 1 
                                                      ELSE 0                                                  END, 
SUBSTRING(CONVERT(VARCHAR(8), HIRE_DATE, 112), 5, 4), SUBSTRING(CONVERT(VARCHAR(8),   
GETDATE(), 112), 5, 4) FROM EMPLOYEE 
--此法对于闰年的2月29日和平年的3月1日是相等的。  
--足年计算法三. 
SELECT *, DATEDIFF(YEAR, HIRE_DATE, GETDATE()) - CASE WHEN DATEPART(DAYOFYEAR, HIRE_DATE) > DATEPART(DAYOFYEAR, GETDATE()) THEN 1 
                                                      ELSE 0                                                  END FROM EMPLOYEE 
--此法比较简单，但是在闰年和平年3月份以后的日期时有1日的差别。  



 


--足月计算法一. 
SELECT *, DATEDIFF(MONTH, HIRE_DATE, GETDATE()) - CASE WHEN DATEADD(MONTH, DATEDIFF(MONTH, HIRE_DATE, GETDATE()), GETDATE()) > GETDATE() THEN 1                                                        ELSE 0                                                   END FROM EMPLOYEE  
--足月计算法二. 
SELECT *, DATEDIFF(MONTH, HIRE_DATE, GETDATE()) - CASE WHEN DAY(HIRE_DATE) > DAY(GETDATE()) THEN 1 
                                                       ELSE 0                                                   END FROM EMPLOYEE 
--根据上面的规律，还可以写出足日、时等等。    
http://blog.csdn.net/misterliwei/archive/2009/12/18/5034037.aspx