SQL server 转换日期和时间

你也许已经注意到，在上一节的例子中，函数GETDATE（）的返回值在显示时只显示到
秒。实际上，SQL Sever 内部时间可以精确到毫秒级（确切地说，可以精确到3.33毫秒）。
要得到不同格式的日期和时间，你需要使用函数CONVERT（）。例如，当下面的这个语
句执行时，显示的时间将包括毫秒：
SELECT CONVERT(VARCHAR(30),GETDATE(),9)
注意例子中数字9 的使用。这个数字指明了在显示日期和时间时使用哪种日期和时间格
式。当这个语句执行时，将显示如下的日期和时间：
…………………………………..
Nov 30 1997 3:29:55:170AM
(1 row(s) affected)
在函数CONVERT（）中你可以使用许多种不同风格的日期和时间格式。表11.1 显示了
所有的格式。
表11.1 日期和时间的类型
类型值标准输出
0 Default mon dd yyyy hh:miAM
1 USA mm/dd/yy
2 ANSI yy.mm.dd
3 British/French dd/mm/yy
4 German dd.mm.yy
5 Italian dd-mm-yy
6 - dd mon yy
7 - mon dd,yy
8 - hh:mi:ss
9 Default + milliseconds--mon dd yyyy
hh:mi:ss:mmmAM(or )
10 USA mm-dd-yy
11 JAPAN yy/mm/dd
12 ISO yymmdd
13 Europe Default + milliseconds--dd mon yyyy
hh:mi:ss:mmm(24h)
14 - hh:mi:ss:mmm(24h)
类型0，9，和13 总是返回四位的年。对其它类型，要显示世纪，把style 值加上100。类型13 和14
返回24小时时钟的时间。类型0，7，和13返回的月份用三位字符表示（用Nov代表November）.
对表11.1 中所列的每一种格式，你可以把类型值加上100 来显示有世纪的年（例如，
00 年将显示为2000年）。例如，要按日本标准显示日期，包括世纪，你应使用如下的语句：
SELECT CONVERT（VARCHAR（30），GETDATE（），111）
在这个例子中，函数CONVERT（）把日期格式进行转换，显示为1997/11/30
抽取日期和时间
在许多情况下，你也许只想得到日期和时间的一部分，而不是完整的日期和时间。例如，
假设你想列出你的站点目录中每个站点被查询的月份。这时你不希望完整的日期和时间把网
页弄乱。为了抽取日期的特定部分，你可以使用函数DATEPART（），象这样：
SELECT site_name ‘Site Name’,
DATEPART(mm,site_entrydate) ‘Month Posted’ FROM site_directory
函数DATEPART（）的参数是两个变量。第一个变量指定要抽取日期的哪一部分；第二
个变量是实际的数据。在这个例子中，函数DATEPART（）抽取月份，因为mm代表月份。下
面是这个SELECT 语句的输出结果：
Site Name Month Posted
………………………………………………………………
Yahoo 2
Microsoft 5
Magicw3 5
(3 row(s) affected)
Month Posted 列显示了每个站点被查询的月份。函数DATEPART（）的返回值是一个整数。
你可以用这个函数抽取日期的各个不同部分，如表11.2所示。
表11.2 日期的各部分及其简写
日期部分简写值
year yy 1753--9999
quarter qq 1--4
month mm 1--12
day of year dy 1--366
day dd 1--31
week wk 1--53
weekday dw 1--7(Sunday--Saturday)
hour hh 0--23
minute mi 0--59
second ss 0--59
milisecond ms 0--999
当你需要进行日期和时间的比较时，使用函数DATEPART（）返回整数是有用的。但是，
上例中的查询结果（2，5）不是十分易读。要以更易读的格式得到部分的日期和时间，你可
以使用函数DATENAME（），如下例所示：
SELECT site_name ‘Site Name’
DATENAME(mm,site_entrydate) ‘Month Posted’
FROM site_directory
函数DATENAME（）和函数DATEPART（）接收同样的参数。但是，它的返回值是一个字符串，
而不是一个整数。下面是上例该用DATENAME（）得到的结果：
Site Name Month Postec
………………………………………………………………….
Yahoo February
Microsoft June
Magicw3 June
(3 row(s) affected)
你也可以用函数DATENAE（）来抽取一个星期中的某一天。下面的这个例子同时抽取一
周中的某一天和日期中的月份：
SELECT site_name ‘Site Name’,
DATENAME(dw,site_entrydate)+ ‘-’ + DATENAME（mm,site_entrydate）
‘Day and Month Posted’ FORM site_directory
这个例子执行时，将返回如下的结果：
Site Name Day and Month Posted
………………………………………………………………………
Yahoo Friday - February
Microsoft Tuesday - June
Magicw3 Monday - June
(3 row(s) affected)
返回日期和时间范围
当你分析表中的数据时，你也许希望取出某个特定时间的数据。你也许对特定的某一天
中――比如说2000 年12 月25 日――访问者在你站点上的活动感兴趣。要取出这种类型的
数据，你也许会试图使用这样的SELECT语句：
SELECT * FROM weblog WHERE entrydate=”12/25/20000”
不要这样做。这个SELECT 语句不会返回正确的记录――它将只返回日期和时间是
12/25/2000 12:00:00:000AM 的记录。换句话说，只有刚好在午夜零点输入的记录才被返回。
注意：
在本节的讨论中，假设字段entrydate是DATETIME 型，而不是SMALLDATETIME 型。本
节的讨论对SMALLDATETIME 型字段也是适用的，不过SMALLDATETIME型字段只能精确到秒。
问题是SQL Sever 将用完整的日期和时间代替部分日期和时间。例如，当你输入一个日
期，但不输入时间时，SQL Sever 将加上缺省的时间“12：00：00：000AM”。当你输入一个
时间，但不输入日期时，SQL Sever 将加上缺省的日期“Jan 1 1900”。
要返回正确的记录，你需要适用日期和时间范围。有不止一种途径可以做到这一点。例
如，下面的这个SELECT 语句将能返回正确的记录：
SELECT * FROM weblog
WHERE entrydate>=”12/25/2000” AND entrydate<”12/26/2000”
这个语句可以完成任务，因为它选取的是表中的日期和时间大于等于12/25/2000
12:00:00:000AM 并小于12/26/2000 12:00:00:000AM 的记录。换句话说，它将正确地返回
2000 年圣诞节这一天输入的每一条记录。
另一种方法是，你可以使用LIKE 来返回正确的记录。通过在日期表达式中包含通配符
“％”，你可以匹配一个特定日期的所有时间。这里有一个例子：
SELECT * FROM weblog WHERE entrydate LIKE ‘Dec 25 2000%’
这个语句可以匹配正确的记录。因为通配符“％”代表了任何时间。
使用这两种匹配日期和时间范围的函数，你可以选择某个月，某一天，某一年，某个小
时，某一分钟，某一秒，甚至某一毫秒内输入的记录。但是，如果你使用LIKE 来匹配秒或
毫秒，你首先需要使用函数CONVERT（）把日期和时间转换为更精确的格式（参见前面“转
换日期和时间”一节）。
比较日期和时间
最后，还有两个日期和时间函数对根据日期和时间取出记录是有用的。使用函数
DATEADD（）和DATEDIFF（），你可以比较日期的早晚。例如，下面的SELECT 语句将显示表
中的每一条记录已经输入了多少个小时：
SELECT entrydate ‘Time Entered’
DATEDIFF(hh,entrydate,GETDATE()) ‘Hours Ago’ FROM weblog
如果当前时间是2000年11 月30 号下午6 点15分，则会返回如下的结果：
Time Entered Hours Ago
…………………………………………………..
Dec 30 2000 4:09PM 2
Dec 30 2000 4:13PM 2
Dec 1 2000 4:09PM 698
(3 row(s) affected)
函数DADEDIFF（）的参数是三个变量。第个变量指定日期的某一部分。在这个例子中，
是按小时对日期进行比较，（要了解日期各部分的详细内容，请参考表11.2）在日期2000
年11 月1 日和2000年11 月30 日的指定时间之间有689 个小时。另外两个参数是要进行比
较的时间。为了返回一个正数，较早的时间应该先给。
函数DATEADD（）把两个日期相加。当你需要计算截止日期这一类的数据时，这个函数
是有用处的。例如，假设访问者必须先注册才能使用你的站点。注册以后，他们可以免费使
用你的站点一个月。要确定什么时候他们的免费时间会用完，你可以使用如下的SELECT 语
句：
SELECT username ‘User Name’,
DATEADD(mm,1,firstvisit_date) ‘Registration Expires’
FROM registration_table
函数DATEADD（）的参数有三个变量。第一个变量代表日期的某一部分（参见表11.2），
这个例子用到了代表月份的mm。第二个变量指定了时间的间隔――在本例中是一个月。最
后一个变量是一个日期，在这个例子中，日期是取自DATETIME 型字段firstvisit_date.假
设当前日期是June 30,2000，这个语句将返回如下的内容：
User Name Registration Expires
……………………………………………………………………………
Bill Gates Jul 30 2000 4:09PM
President Clinton Jul 30 2000 4:13PM
William Shakespeare Jul 1 2000 4:09PM
(3 row(s) affected)
注意：
与你预料的相反，使用函数DATEADD（）把一个日期加上一个月，它并不加上30 天。
这个函数只简单地把月份值加1。这意味着在11 月注册的人将比在2 月注册的人多得到2
天或3 天的时间。要避免这个问题，你可以用函数DATEADD（）直接增加天数，而不是月份