使用日期和时间数据
本主题以下各部分提供了有关使用日期和时间数据类型及函数的信息和示例。有关所有 Transact-SQL 日期和时间数据类型及函数的概述，请参阅日期和时间数据类型及函数 (Transact-SQL)。
将运算符与日期和时间数据类型一起使用
使用日期和时间格式
字符串文字的日期和时间格式
未分隔的字符串格式
ISO 8601 格式
字母日期格式
数值日期格式
时间格式
ODBC 日期时间格式
转换 date、time、datetime2 和 datetimeoffset
字符串文字与 time(n)、date、datetime2(n) 和 datetimeoffset(n) 之间的转换
日期和时间数据类型之间的转换
对 time、date、datetime2 和 datetimeoffset 使用 CAST 和 CONVERT
CONVERT 函数的样式参数
SQL Server 日期和时间的局限性
下级客户端的向后兼容性
示例
将运算符与日期和时间数据类型一起使用
所有日期和时间数据类型都支持关系运算符（<、<=、>、>=、<>）、比较运算符（=、<、<=、>、>=、<>、!<、!>）以及逻辑运算符和布尔谓词（IS NULL、IS NOT NULL、IN、BETWEEN、EXISTS、NOT EXISTS 和 LIKE）。
日期和时间算术运算符
使用日期和时间格式
字符串文字格式会影响应用程序中数据呈现给用户的方式，但不会影响 SQL Server 中的基础整数存储格式。但是，SQL Server 可能会将由应用程序或用户输入的用来存储或提供给某个日期函数的字符串文字格式日期值解释为不同的日期。具体解释要综合字符串文字格式、数据类型、运行时 SET DATEFORMAT 和 SET LANGUAGE 以及默认语言选项设置等因素来决定。
有些字符串文字格式不受这些设置影响。除非知道这些设置对于要使用的格式是正确的，否则请考虑使用不依赖于这些设置的格式。ISO 8601 格式不依赖于这些设置，它是一种国际标准。Transact-SQL 使用依赖于系统设置的字符串文字格式，其可移植性较差。
若要确定适用于下级客户端的默认字符串文字格式，请参阅有关各日期和时间数据类型的主题。有关所有 Transact-SQL 日期和时间数据类型及函数的概述，请参阅日期和时间数据类型及函数 (Transact-SQL)。
date、datetime2 和 datetimeoffset 类型不支持 ydm 日期格式。对它们使用此格式会引发运行时错误。
字符串文字的日期和时间格式
下表列出了各种不同的日期和时间字符串格式。我们建议使用不依赖于 DATEFORMAT 且适用于多种语言的日期时间格式。仅有 ISO 8601 格式 '1998-02-23T14:23:05' 和 '1998-02-23T14:23:05 -08:00' 是国际标准格式。它们不依赖于 DATEFORMAT 或默认登录语言，适用于多种语言。
日期时间部分 |
格式类型 |
格式示例 |
可与其他格式结合使用 |
依赖于 DATEFORMAT |
多语言 |
|---|---|---|---|---|---|
日期 |
未分隔 ISO 8601 |
'19980223' |
是 |
否 |
是 |
日期 |
数值 |
'02/23/1998' |
是 |
是 |
否 (DATEFORMAT) |
日期 |
ISO 8601 数值 |
'1998-02-23' |
是 |
否 |
否 |
日期 |
字母 |
'23 February 1998' |
是 |
否 |
否 （月份或简写月份） |
日期 |
ODBC 日期 |
{d '1998-02-23'} |
否 |
否 |
是 |
时间 |
ISO 8601 时间 |
'14:23:05' '10:00:00.123456' |
是 |
否 |
是 |
时间 |
ODBC 时间 |
{t '14:23:05'} |
否 |
否 |
是 |
日期时间 |
ODBC 日期时间 |
{ts '1998-02-23 14:23:05'} |
否 |
否 |
是 |
日期时间 |
ISO 8601 |
'1998-02-23T14:23:05' '1998-02-23T14:23:05 -08:00' |
否 |
否 |
是（date、datetime2、datetimeoffset）。 |
日期时间 |
ANSI SQL 标准 |
'1998-02-23 14:23:05' '1998-02-23 14:23:05 -08:00' |
否 |
否（datetime2、datetimeoffset） 是 (datetime) |
是（date、datetime2、datetimeoffset）。 |
日期时间 |
日期和时间的组合 |
'19980223 14:23:05' '02/23/1998 2:23:05 PM' '1998-02-23 10:00:00.123' '23 Feb 1998 14:23:05' |
否 |
是 （日期部分） |
否 |
时区 |
时区格式 |
'+12:00' '01:00' '-08:00' 'Z' |
是 |
否 |
是 |
下列语句显示了 SET LANGUAGE 和 SET DATEFORMAT 设置的效果。
DECLARE @Today date = '12/1/2003';
DECLARE @MyTime time = '12:30:22';
DECLARE @MyDatetimeoffset datetimeoffset = '12/1/2003 12:30:22 -5:00';
SET LANGUAGE Italian
-- Returns: Changed language setting to Italiano.
SELECT DATENAME(month, @Today) AS 'Month Name';
-- Returns: dicembre
SELECT CONVERT(VARCHAR(30), @MyDatetimeoffset) AS 'DATETIMEOFFSET';
-- Returns: dic 1 2003 12:30PM -05:00
SELECT DATENAME(hour, @MyTime) As 'Hour Name';
-- Returns: 12
SET LANGUAGE us_english;
-- Returns: Changed language setting to us_english.
SELECT DATENAME(month, @Today) AS 'Month Name';
-- Returns: December
SELECT CONVERT(VARCHAR(30), @MyDatetimeoffset) AS 'DATETIMEOFFSET';
-- Returns: Dec 1 2003 12:30PM -05:00
SELECT DATENAME(hour, @MyTime) As 'Hour Name';
-- Returns: 12
GO
-- Set date format to month, day, year.
SET DATEFORMAT mdy;
GO
DECLARE @datevar date = '12/31/1998';
SELECT @datevar AS DateVar;
-- Returns: 1998-12-31
GO
-- Set date format to year, day, month.
SET DATEFORMAT ydm;
GO
DECLARE @datevar datetimeoffset = '1998/31/12 12:30:22 -05:00';
SELECT @datevar AS DateVar;
-- Returns: Msg 241, Conversion failed when converting
-- date and/or time from character string.
GO
-- Set date format to year, month, day.
SET DATEFORMAT ymd;
GO
DECLARE @datevar date = '12/31/1998';
SELECT @datevar AS DateVar;
-- Returns: 1998-12-31
GO
未分隔的字符串格式
您可以将日期数据指定为未分隔的字符串。可使用四位、六位或八位数字，空字符串，或不带日期值的时间值来指定日期数据。
SET DATEFORMAT 会话设置不适用于全是数值的日期项，例如不带分隔符的数值项。六位或八位字符串始终被解释为 ymd。月和日必须始终是两位数字。
这就是一个有效的未分隔的字符串格式：[19]960415
只含四位数字的字符串被解释为年份。月和日设置为 1 月 1 日。只指定四位数字时，必须包括世纪。
ISO 8601 格式
带时间的 ISO 8601 日期格式如下所示：
YYYY-MM-DDThh:mm:ss[.nnnnnnn][{+|-}hh:mm]
YYYY-MM-DDThh:mm:ss[.nnnnnnn]Z（UTC，协调世界时）
T 表示后面是日期时间值的时间部分。
若要使用 ISO 8601 格式，必须按此格式指定每一个元素。这包括 T、冒号 (:)、+ 或 - 号以及句点 (.)。方括号表示小数秒或时区偏移量部分是可选的。
时间部分按 24 小时制格式指定。
使用 ISO 8601 格式的优势在于它是一个国际标准。使用此格式指定的日期和时间值很明确。此格式不受 SET DATEFORMAT、SET LANGUAGE 或登录时的默认语言设置影响。
下面是两个按 ISO 8601 格式指定的日期时间值的示例：
2004-05-23T14:25:10
2004-05-23T14:25:10.1234567+07:00
字母日期格式
您可以用名称指定月份，例如，可以用英文 April 或缩写 Apr 指定。这些应在会话的 LANGUAGE 设置中指定，例如，LANGUAGE 设置为法语时可以指定 avril 或 avr。逗号是可选的，且忽略大小写。
下面是使用字母日期格式的一些准则：
日期和时间数据要放在单引号 (') 内。
如果只指定年份的最后两位数字，则小于两位数年份截止配置选项值最后两位数字的值与截止年份在同一个世纪。大于或等于该选项值的值在截止年份的上一世纪。例如，如果“两位数年份截止”为 2050（默认值），则 25 将被解释为 2025，50 将被解释为 1950。为避免模糊不清，请使用四位数的年份。
如果没有指定日，则默认值为当月第一天。
当按字母形式指定月份时，SET DATEFORMAT 会话设置不起作用。
以下格式是 SQL Server 日期数据的有效字母格式。方括号中的字符是可选的。
Apr[il] [15][,] 1996
Apr[il] 15[,] [19]96
Apr[il] 1996 [15]
[15] Apr[il][,] 1996
15 Apr[il][,][19]96
15 [19]96 apr[il]
[15] 1996 apr[il]
1996 APR[IL] [15]
1996 [15] APR[IL]
数值日期格式
您可以用数值月份指定日期数据。例如 5/20/97 表示 1997 年 5 月 20 日。当使用数值日期格式时，请在字符串中使用正斜杠符号 (/)、连字符 (-) 或句点 (.) 作为分隔符来指定年、月、日。此字符串必须采用以下形式：
数字分隔符数字分隔符数字 [时间] [时间]
以下数值格式是有效的：
[0]4/15/[19]96 -- (mdy)
[0]4-15-[19]96 -- (mdy)
[0]4.15.[19]96 -- (mdy)
[0]4/[19]96/15 -- (myd)
15/[0]4/[19]96 -- (dmy)
15/[19]96/[0]4 -- (dym)
[19]96/15/[0]4 -- (ydm)
[19]96/[0]4/15 -- (ymd)
会话的默认语言 DATEFORMAT 由登录时的默认语言、SET LANGUAGE 语句或 SET DATEFORMAT 语句设置。当语言由默认登录或 SET LANGUAGE 语句设置为 us_english 时，日期的默认顺序为 mdy。
可以使用 SET DATEFORMAT 语句更改日期顺序。SET DATEFORMAT 的设置决定了如何解释日期值。如果顺序和设置不匹配，这些值将由于超出范围而不会被解释成日期，或者被错误地解释。例如，12/10/08 可能会解释成六个日期中的某一个，具体取决于 DATEFORMAT 的设置。四位数年份将解释为年。
时间格式
SQL Server 能够识别下列时间数据格式。用单引号 (') 把每一种格式引起来。
14:30
14:30[:20:999]
14:30[:20.9]
4am
4 PM
[0]4[:30:20:500]AM
下列语句显示了为 CAST 函数提供不同的输入时得到的返回值。
SELECT CAST('01/01/2000 14:30' AS datetime2)
--Returns: 2000-01-01 14:30:00.0000000
SELECT CAST('01/01/2000 14:30:20:999' AS datetime2)
-- Returns: 2000-01-01 14:30:20.9990000
SELECT CAST('01/01/2000 14:30:20.9' AS datetime2)
-- Returns: 2000-01-01 14:30:20.9000000
SELECT CAST('01/01/2000 4am' AS datetime2)
-- Returns: 2000-01-01 04:00:00.0000000
SELECT CAST('01/01/2000 4 PM' AS datetime2)
-- Returns: 2000-01-01 16:00:00.0000000
SELECT CAST('01/01/2000 04:30:20:500AM' AS datetime2)
-- Returns: 2000-01-01 04:30:20.5000000
SELECT CAST('01/01/2000 04:30:20:500 AM' AS datetime2)
-- Returns: 2000-01-01 04:30:20.5000000
GO
可以指定 AM 或 PM 后缀来表明时间值是在中午 12 点之前还是之后。AM 或 PM 忽略大小写。
小时可按 12 小时或 24 小时制指定。小时值解释如下。
如果小时值为 00，则不论是否指定了 AM，它都表示午夜 (AM) 之后的小时。当小时值等于 00 时，不能指定 PM。
如果 AM 和 PM 均未指定，则小时值为 01 到 11 时，表示中午以前的小时。当指定 AM 时，它们也表示中午以前的小时。当指定 PM 时，它们表示中午以后的小时。
如果未指定 AM 或 PM，小时值 12 表示始于中午的小时。如果指定 AM，则表示始于午夜的小时。如果指定 PM，则表示始于中午的小时。例如：12:01 是指中午过后 1 分钟，与 12:01 PM 的含义相同，而 12:01 AM 是指午夜过后 1 分钟。指定 12:01 AM 与指定 00:01 或 00:01 AM 相同。
如果未指定 AM 或 PM，小时值 13 到 23 表示中午以后的小时。如果指定 PM，它们也表示中午以后的小时。当小时值为 13 到 23 时，不能指定 AM。
小时值为 24 时无效；请用 12:00 AM 或 00:00 表示午夜。
可以在毫秒之前加上冒号 (:) 或者句点 (.)。如果前面加冒号，这个数字表示千分之一秒。如果前面加句点，单个数字表示十分之一秒，两个数字表示百分之一秒，三个数字表示千分之一秒。例如，12:30:20:1 表示到了 12:30 后又过了二十又千分之一秒；12:30:20.1 表示到了 12:30 后又过了二十又十分之一秒。
ODBC 日期时间格式
ODBC API 定义了转义序列来表示日期和时间值，ODBC 将这些值称为时间戳数据。OLE DB 语言定义 (DBGUID-SQL) 和 Microsoft OLE DB Provider for SQL Server 也支持这种 ODBC 时间戳格式。使用 ADO、OLE DB 和基于 ODBC 的 API 的应用程序可以使用这种 ODBC 时间戳格式来表示日期和时间。
SQL Server 始终将 ODBC 数据视为属于 datetime 数据类型。
ODBC 时间戳的转义序列格式为：
{ literal_type 'constant_value' }
literal_type
指定转义序列的类型。下面是 literal_type 的有效参数。d = 仅日期
t = 仅时间
ts = 时间戳（时间 + 日期）
'constant_value'
是转义序列的值。constant_value 必须遵循下面每个 literal_type 的格式。literal_type
constant_value 格式
d
YYYY-MM-DD
t
hh:mm:ss[.fff]
ts
YYYY-MM-DDhh:mm:ss[.fff]
下面是 ODBC 时间和日期常量的示例：
{ ts '1998-05-02 01:23:56.123' }
{ d '1990-10-02' }
{ t '13:33:41' }
不要混淆 ODBC 和 OLE DB timestamp 数据类型名称与 Transact-SQLtimestamp 数据类型名称。ODBC 和 OLE DB timestamp 数据类型记录日期和时间。Transact-SQLtimestamp 数据类型是一个二进制数据类型，不具有与时间有关的值。
转换 date、time、datetime2 和 datetimeoffset
不同日期类型之间有两种转换：显式和隐式。隐式转换的实现不使用 CAST 或 CONVERT 函数。显式转换的实现需要使用 CAST 或 CONVERT 函数。
字符串文字与 time(n)、date、datetime2(n) 和 datetimeoffset(n) 之间的转换
如果字符串所有部分的格式均有效，则允许从字符串文字转换为日期和时间类型。否则，将引发运行时错误。
从日期和时间类型向字符串文字进行的未指定样式的隐式转换或显式转换将采用当前会话的默认格式。
对于从 date、time、datetime2 和 datetimeoffset 数据类型向字符串进行的隐式转换，将采用 SQL 标准格式 YYY-MM-DD hh:mi:ss.[nnnnnnn]，即 CONVERT 样式 121。对于 datetime 和 smalldatetime 数据类型，将采用 CONVERT 样式 0 格式，即 mon dd yyyy hh:miAM（或 PM）。
下表显示了 date、time、datetime2 和 datetimeoffset 类型与字符串文字之间的转换规则。
输入字符串文字 |
date |
time(n) |
datetime2(n) |
datetimeoffset(n) |
|---|---|---|---|---|
ODBC DATE |
见第 1 条注释。 |
见第 1 条注释。 |
见第 1 条注释。 |
见第 1 条注释。 |
ODBC TIME |
见第 1 条注释。 |
见第 1 条注释。 |
见第 1 条注释。 |
见第 1 条注释。 |
ODBC DATETIME |
见第 1 条注释。 |
见第 1 条注释。 |
见第 1 条注释。 |
见第 1 条注释。 |
仅 DATE |
无庸赘述 |
提供默认值 |
TIME 部分默认为 00:00:00。 |
TIME 部分默认为 00:00:00。TIMEZONE 默认为 +00:00。 |
仅 TIME |
提供默认值 |
无庸赘述 |
DATE 部分默认为 1900-1-1。 |
DATE 部分默认为 1900-1-1。TIMEZONE 将默认为 +00:00。 |
仅 TIMEZONE |
提供默认值 |
提供默认值 |
提供默认值 |
提供默认值 |
DATE + TIME |
使用输入字符串的 DATE 部分。 |
使用输入字符串的 TIME 部分。 |
无庸赘述 |
TIMEZONE 默认为 +00:00。 |
DATE + TIMEZONE |
不允许 |
不允许 |
不允许 |
不允许 |
TIME + TIMEZONE |
提供默认值 |
使用输入字符串的 TIME 部分。 |
DATE 部分默认为 1900-1-1。忽略 TIMEZONE 输入。 |
DATE 部分默认为 1900-1-1。 |
DATE + TIME + TIMEZONE |
将使用本地 DATETIME 的 DATE 部分。 |
将使用本地 DATETIME 的 TIME 部分。 |
将使用本地 DATETIME。 |
无庸赘述 |
转换说明
ODBC 字符串文字映射到 datetime 数据类型。从 ODBC DATETIME 文字到 date、time、datetime2 或 datetimeoffset 类型的任何赋值操作都会导致在 datetime 与这些类型之间按照转换规则的定义进行隐式转换。
datetime 的小数秒精度精确到三百分之一秒（相当于 3.33 毫秒或 0.00333 秒）。值舍入到 .000、.003 或 .007 秒三个增量。'08/22/1995 10:15:19:999' 将进行舍入，因为“.999”超出了精度。
对于 time(3)、datetime2(3) 或 datetimeoffset(3)，小数秒精度精确到一毫秒。因此，'1995-8-22 10:15:19:999' 不会进行舍入。
对于 hh 和 mm，输入 TIMEZONE 偏移量部分都应该始终是两位数，且必须带有符号（+ 或 –）。
日期和时间数据类型之间的转换
本部分中的表介绍下列各种日期和时间数据类型如何转换为其他日期和时间数据类型：
date
time(n)
datetime
smalldatetime
datetimeoffset(n)
datetime2
date 数据类型
下表介绍当 date 数据类型转换为其他日期和时间数据类型时发生的情况。
要转换成的数据类型 |
转换详细信息 |
|---|---|
time(n) |
转换失败，并引发错误消息 206：“操作数类型冲突: date 与 time 不兼容”。 |
datetime |
复制日期。下面的代码显示将 date 值转换为 datetime 值的结果。 |
smalldatetime |
当 date 值在 smalldatetime 的范围内时，会复制日期部分，并将时间部分设置为 00:00:00.000。当 date 值不在 smalldatetime 值的范围内时，会引发错误消息 242：“从 date 数据类型到 smalldatetime 数据类型的转换生成超出范围的值。”，smalldatetime 值将设置为 NULL。 下面的代码显示将 date 值转换为 smalldatetime 值的结果。 |
datetimeoffset(n) |
复制日期，时间设置为 00:00.0000000 +00:00。 下面的代码显示将 date 值转换为 datetimeoffset(3) 值的结果。 |
datetime2(n) |
复制日期部分，时间部分设置为 00:00.000000。 下面的代码显示将 date 值转换为 datetime2(3) 值的结果。 |
time(n) 数据类型
下表介绍当 time 数据类型转换为其他日期和时间数据类型时发生的情况。
要转换成的数据类型 |
转换详细信息 |
|---|---|
time(n) |
复制时、分、秒。当目标精度小于源精度时，将截断秒的小数部分，以适合目标精度。 下面的示例显示将 time(4) 值转换为 time(3) 值的结果。 |
date |
转换失败，并引发错误消息 206：“操作数类型冲突: date 与 time 不兼容”。 |
datetime |
复制时、分、秒值，日期部分设置为“1900-01-01”。当 time(n) 值的秒的小数部分精度大于三位时，datetime 结果将被截断。 下面的代码显示将 time(4) 值转换为 datetime 值的结果。 |
smalldatetime |
日期设置为“1900-01-01”，复制小时和分钟值。秒和秒的小数部分设置为 0。 下面的代码显示将 time(4) 值转换为 smalldatetime 值的结果。 |
datetimeoffset(n) |
日期设置为“1900-01-01”，复制时间。时区偏移量设置为 +00:00。当 time(n) 值的秒的小数部分精度大于 datetimeoffset(n) 值的精度时，该值将被截断，以适合目标精度。 下面的示例显示将 time(4) 值转换为 datetimeoffset(3) 类型的结果。 |
datetime2(n) |
日期设置为“1900-01-01”，复制时间部分，时区偏移量设置为 00:00。当 datetime2(n) 值的秒的小数部分精度大于 time(n) 值时，该值将被截断，以适合目标精度。 下面的示例显示将 time(4) 值转换为 datetime2(2) 值的结果。 |
datetime 数据类型
下表介绍当 datetime 数据类型转换为其他日期和时间数据类型时发生的情况。
要转换成的数据类型 |
转换详细信息 |
|---|---|
date |
复制年、月、日。时间部分设置为 00:00:00.000。 下面的代码显示将 date 值转换为 datetime 值的结果。 |
time(n) |
复制时间部分，日期部分设置为“1900-01-01”。当 time(n) 值的小数部分精度大于三位时，该值将被截断，以适合目标精度。 下面的示例显示将 time(4) 值转换为 datetime 值的结果。 |
smalldatetime |
复制小时和分钟。秒和秒的小数部分设置为 0。 下面的代码显示将 smalldatetime 值转换为 datetime 值的结果。 |
datetimeoffset(n) |
复制日期和时间部分。时区被截断。当 datetimeoffset(n) 值的小数部分精度大于三位时，该值将被截断。 下面的示例显示了将 datetimeoffset(4) 值转换为 datetime 值的结果。 |
datetime2(n) |
复制日期和时间。当 datetime2(n) 值的小数部分精度大于三位时，该值将被截断。 下面的示例显示了将 datetime2(4) 值转换为 datetime 值的结果。 |
smalldatetime 数据类型
下表介绍当 smalldatetime 数据类型转换为其他日期和时间数据类型时发生的情况。
要转换成的数据类型 |
转换详细信息 |
|---|---|
date |
复制年、月、日。 下面的代码显示将 smalldatetime 值转换为 date 值的结果。 |
time(n) |
复制时、分、秒。秒的小数部分设置为 0。 下面的代码显示将 smalldatetime 值转换为 time(4) 值的结果。 |
datetime |
smalldatetime 值复制到 datetime 值。秒的小数部分设置为 0。 下面的代码显示将 smalldatetime 值转换为 datetime 值的结果。 |
datetimeoffset(n) |
smalldatetime 值复制到 datetimeoffset(n) 值。秒的小数部分设置为 0，时区偏移量设置为 +00:0。 下面的代码显示将 smalldatetime 值转换为 datetimeoffset(4) 值的结果。 |
datetime2(n) |
smalldatetime 值复制到 datetime2(n) 值。秒的小数部分设置为 0。 下面的代码显示将 smalldatetime 值转换为 datetime2(4) 值的结果。 |
datetimeoffset(n) 数据类型
下表介绍当 datetimeoffset(n) 数据类型转换为其他日期和时间数据类型时发生的情况。
要转换成的数据类型 |
转换详细信息 |
|---|---|
date |
复制年、月、日。 下面的代码显示将 datetimeoffset(4) 值转换为 date 值的结果。 |
time(n) |
复制时、分、秒和秒的小数部分。时区值被截断。当 datetimeoffset(n) 值的精度大于 time(n) 值的精度时，该值被截断。 下面的代码显示将 datetimeoffset(4) 值转换为 time(3) 值的结果。 |
datetime |
复制日期和时间值，时区被截断。当 datetimeoffset(n) 值的小数部分精度大于三位时，该值将被截断。 下面的代码显示将 datetimeoffset(4) 值转换为 datetime 值的结果。 |
smalldatetime |
复制日期、小时和分钟。秒设置为 0。 下面的代码显示将 datetimeoffset(3) 值转换为 smalldatetime 值的结果。 |
datetime2(n) |
日期和时间复制到 datetime2 值中，时区被截断。当 datetime2(n) 值的精度大于 datetimeoffset(n) 值的精度时，秒的小数部分将被截断，以适合目标精度。 下面的代码显示将 datetimeoffset(4) 值转换为 datetime2(3) 值的结果。 |
datetime2 数据类型
下表介绍当 datetime2 数据类型转换为其他日期和时间数据类型时发生的情况。
要转换成的数据类型 |
转换详细信息 |
|---|---|
date |
复制年、月、日。 下面的代码显示将 datetime2(4) 值转换为 date 值的结果。 |
time(n) |
复制时、分、秒和秒的小数部分。 下面的代码显示将 datetime2(4) 值转换为 time(3) 值的结果。 |
datetime |
复制日期和时间值。当 datetimeoffset(n) 值的小数部分精度大于三位时，该值将被截断。 下面的代码显示将 datetime2 值转换为 datetime 值的结果。 |
smalldatetime |
复制日期、小时和分钟。秒设置为 0。 下面的代码显示将 datetime2 值转换为 smalldatetime 值的结果。 |
datetimeoffset(n) |
datetime2(n) 值复制到 datetimeoffset(n) 值。时区偏移量设置为 +00:0。当 datetime2(n) 值的精度大于 datetimeoffset(n) 值的精度时，该值将被截断，以适合目标精度。 下面的代码显示将 datetime2(5) 值转换为 datetimeoffset(3) 值的结果。 |
对 time、date、datetime2 和 datetimeoffset 使用 CAST 和 CONVERT
此部分提供有关在各个日期和时间数据类型之间进行转换的信息。
目标类型为 datetimeoffset 的转换
- 只要带时区的 datetimeoffset 值是从不带时区的值隐式得来的（例如，通过简单的赋值操作），就会将不带时区的值视为本地的，并会用该值减去当前默认时区 (00:00) 偏移量以得出 UTC。
- 对于以下转换，当您将非时区数据类型转换为 datetimeoffset 时，始终会追加 UTC 时区 (00:00)：
date 至datetimeoffset
time 至datetimeoffset
datetime2 至datetimeoffset
datetime 或 smalldatetime 至 datetimeoffset
不带时区格式的有效 date、time 或 datetime 中的字符串文字至 datetimeoffset
源类型为 datetimeoffset 的转换
从 datetimeoffset 转换为以下非时区类型时，样式 0（默认值）始终指示返回的 date、time、datetime2、datetime 或 smalltime 值采用保留的时区偏移量的本地格式；样式 1 始终指示采用 UTC 格式。
只要不带时区的日期或时间值是通过下列某种转换隐式得来的，就会将 datetimeoffset 值视为 UTC。保留的时区偏移量将加到该值上以得出本地时间。结果（不带任何时区偏移量）将采用本地时间格式。
datetimeoffset 至date
datetimeoffset 至time
datetimeoffset 至datetime2
datetimeoffset 至 datetime 或 smalldatetime
样式 0 和 1 不能用于 datetimeoffset 至字符串的转换。应先从 datetimeoffset 转换为 datetime2 或 datetime，再转换为varchar 或 char。
如果现有 CONVERT 样式包含时间部分，并且是从 datetimeoffset 转换为字符串，则会包含时区偏移量（样式 127 除外）。如果不希望包含时区偏移量，可以先使用 CAST 转换为 datetime2，再转换为字符串。
所有现有日期和时间样式都将应用于任何从 datetimeoffset 至字符串的转换，并会保留时区偏移量。
对于 hh 和 mm，输入字符串时区偏移量部分都应该始终是两位数，并且必须带有符号（+ 或 –）。
转换过程中丢弃的信息
从 datetime2 或 datetimeoffset 转换为 date 时，不进行舍入，并且日期部分是显式提取的。对于 datetimeoffset，将对本地日期和时间进行提取，而不是对 UTC 值进行提取。
对于从 datetimeoffset 至 date、time、datetime2、datetime 或 smalldatetime 的任何隐式转换，转换都是根据本地日期和时间值进行的（转换为持久性时区偏移量）。例如，将 datetimeoffset(3) 值 2006-10-21 12:20:20.999 -8:00 转换为 time(3) 时，结果为 12:20:20.999，而不是 20:20:20.999(UTC)。
转换中的截断
- 允许从高精度时间值转换为低精度值。高精度值将被截断，以适合低精度类型。
小数秒的转换
如果样式包含时间格式 hh:mm:ss.mmm，则对于 time(n)、datetime2(n) 和 datetimeoffset(n)，该格式将变为 hh:mm:ss.[nnnnnnn]。位数取决于类型指定情况。如果只需要毫秒精度，可先转换为 datetime2(3)，再转换为字符串。
对于样式 9、109、13、113、21、121、130 和 131，将 time、datetime2 和 datetimeoffset 类型转换为字符串时，不支持小数秒之前的冒号 (:) 分隔符。采用上述任何样式的输出字符串格式都将转换为一个句点 (.)。
CONVERT 函数的样式参数
下表列出了 CONVERT 函数样式参数的 date、time、datetime2 和 datetimeoffset 值的示例。有关样式的详细信息，请参阅 CAST 和 CONVERT (Transact-SQL) 的“参数”部分。
样式 |
Associated standard |
Input/Output (3) format |
date |
time(n) |
datetime2(n) |
datetimeoffset(n) |
|---|---|---|---|---|---|---|
0 或 100（1、2） |
默认格式 |
mon dd yyyy hh:miAM（或 PM） |
Jan 1 2001 |
12:20PM |
Jan 1 2001 12:20PM |
Jan 1 2001 12:20PM -08:00 |
101 |
美国 |
mm/dd/yyyy |
01/01/2001 |
- |
01/01/2001 |
01/01/2001 |
102 |
ANSI |
yy.mm.dd |
2001.01.01 |
- |
2001.01.01 |
2001.01.01 |
103 |
英国/法国 |
dd/mm/yy |
01/01/2001 |
- |
01/01/2001 |
01/01/2001 |
104 |
德国 |
dd.mm.yy |
01.01.2001 |
- |
01.01.2001 |
01.01.2001 |
105 |
意大利 |
dd-mm-yy |
01-01-2001 |
- |
01-01-2001 |
01-01-2001 |
106(1) |
- |
dd mon yy |
01 Jan 2001 |
- |
01 Jan 2001 |
01 Jan 2001 |
107(1) |
- |
Mon dd, yy |
Jan 01, 2001 |
- |
Jan 01, 2001 |
Jan 01, 2001 |
108 |
- |
hh:mi:ss |
- |
12:20:20 |
12:20:20 |
12:20:20 |
9 或 109（1、2） |
默认格式 + 毫秒 |
mon dd yyyy hh:mi:ss:mmmAM（或 PM） |
Jan 1 2001 |
12:20:20.1234567AM |
Jan 1 2001 12:20:20.1234567PM |
Jan 1 2001 12:20:20:1230000PM -08:00 |
110 |
美国 |
mm-dd-yy |
01-01-2001 |
- |
01-01-2001 |
01-01-2001 |
111 |
日本 |
yy/mm/dd |
2001/01/01 |
- |
2001/01/01 |
2001/01/01 |
112 |
ISO |
yymmdd |
20010101 |
- |
20010101 |
20010101 |
13 或 113（1、2） |
欧洲默认格式 + 毫秒 |
dd mon yyyy hh:mi:ss:mmm(24h) |
01 Jan 2001 |
12:20:20.1234567 |
01 Jan 2001 12:20:20.1234567 |
01 Jan 2001 12:20:20:1230000 -08:00 |
114 |
- |
hh:mi:ss:mmm(24h) |
- |
12:20:20.1234567 |
12:20:20.1234567 |
12:20:20:1230000 -08:00 |
20 或 120 (2) |
ODBC 规范 |
yyyy-mm-dd hh:mi:ss(24h) |
2001-01-01 |
12:20:20 |
2001-01-01 12:20:20 |
2001-01-01 12:20:20 -08:00 |
21 或 121 (2) |
ODBC 规范（带毫秒） |
yyyy-mm-dd hh:mi:ss.mmm(24h) |
2001-01-01 |
12:20:20.1234567 |
2001-01-01 12:20:20.1234567 |
2001-01-01 12:20:20.1230000 -08:00 |
126 (4) |
ISO8601 |
yyyy-mm-ddThh:mi:ss.mmm（无空格） |
2001-01-01 |
12:20:20.1234567 |
2001-01-01T 12:20:20.1234567 |
2001-01-01T 12:20:20.1234567 |
127(6, 7) |
带时区 Z 的 ISO8601。 |
yyyy-mm-ddThh:mi:ss.mmmZ （无空格） |
2001-01-01 |
12:20:20.1234567Z |
2001-01-01T 12:20:20.1234567Z |
2001-01-01T20:20:20.1230000Z |
130 (1,2) |
回历 (5) |
dd mon yyyy hh:mi:ss:mmmAM |
01 Jan 2001 |
12:20:20.1230000PM |
01 Jan 2001 12:20:20.1230000PM |
1 Jan 2001 12:20:20:1230000PM -08:00 |
131 (2) |
回历 (5) |
dd/mm/yy hh:mi:ss:mmmAM |
01/01/2001 |
12:20:20.1230000PM |
01/01/2001 12:20:20.1230000PM |
01/01/2001 12:20:20.1230000PM -08:00 |
1 这些样式值返回不确定的结果。包括所有 (yy)（不带世纪数位）样式和一部分 (yyyy)（带世纪数位）样式。
2 默认值（style0 或 100、9 或 109、13 或 113、20 或 120 以及 21 或 121）始终返回世纪数位 (yyyy)。
3 转换为 datetime 时输入；转换为字符数据时输出。
4 为用于 XML 而设计。对于从 datetime 或 smalldatetime 到字符数据的转换，输出格式如上表所述。
5 回历是有多种变体的日历系统。SQL Server 使用科威特算法。
6 仅在从字符数据转换为 datetime 或 smalldatetime 时才支持此样式。仅表示日期部分或时间部分的字符数据转换为 datetime 或 smalldatetime 数据类型时，未指定的时间部分将设置为 00:00:00.000，未指定的日期部分将设置为 1900-01-01。
7 使用可选的时区指示符 (Z)，更便于将含有时区信息的 XML datetime 值映射到不含时区的 SQL Serverdatetime 值。Z 是时区 UTC-0 的指示符。
SQL Server 日期和时间的局限性
在下面的列表中，日期和时间是指包含日期或时间部分的任何日期时间数据类型。
不支持在服务器端采用夏时制 (DST) 表示 datetimeoffset。
不支持采用儒略历表示日期。
不支持午夜时间采用“24”点表示形式。
不支持超过“59”的“闰秒”时间。
不支持“一纳秒”或更高的小数秒时间精度。
不支持时间包含时区。
不支持 SQL 标准 INTERVAL 操作。
下级客户端的向后兼容性
某些下级客户端不支持 SQL Server 2008 中新增的time、date、datetime2 和 datetimeoffset 数据类型。下表显示了 SQL Server 2008 上级实例与下级客户端之间的类型映射。
SQL Server 2008 数据类型 |
传递给下级客户端的默认字符串文字格式 |
下级 ODBC |
下级 OLEDB |
下级 JDBC |
下级 SQLCLIENT |
|---|---|---|---|---|---|
time |
hh:mm:ss[.nnnnnnn] |
SQL_WVARCHAR 或 SQL_VARCHAR |
DBTYPE_WSTR 或 DBTYPE_STR |
Java.sql.String |
String 或 SqString |
date |
YYYY-MM-DD |
SQL_WVARCHAR 或 SQL_VARCHAR |
DBTYPE_WSTR 或 DBTYPE_STR |
Java.sql.String |
String 或 SqString |
datetime2 |
YYYY-MM-DD hh:mm:ss[.nnnnnnn] |
SQL_WVARCHAR 或 SQL_VARCHAR |
DBTYPE_WSTR 或 DBTYPE_STR |
Java.sql.String |
String 或 SqString |
datetimeoffset |
YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm |
SQL_WVARCHAR 或 SQL_VARCHAR |
DBTYPE_WSTR 或 DBTYPE_STR |
Java.sql.String |
String 或 SqString |
示例
A. 比较各种日期和时间数据类型
下例比较将一个字符串文字分别转换为各种日期和时间数据类型时所产生的结果。如果字符串文字的小数秒精度高于 smalldatetime 或 datetime 所允许的小数秒精度，则试图使用 CAST 转换该字符串文字将引发错误。
SELECT
CAST('2007-05-08 12:35:29. 1234567 +12:15' AS time(7)) AS 'time'
,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS date) AS 'date'
,CAST('2007-05-08 12:35:29.123' AS smalldatetime) AS
'smalldatetime'
,CAST('2007-05-08 12:35:29.123' AS datetime) AS 'datetime'
,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS datetime2(7)) AS
'datetime2'
,CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS
'datetimeoffset';
数据类型 |
输出 |
|---|---|
Time |
12:35:29.1234567 |
Date |
2007-05-08 |
Smalldatetime |
2007-05-08 12:35:00 |
Datetime |
2007-05-08 12:35:29.123 |
datetime2(7) |
2007-05-08 12:35:29.1234567 |
datetimeoffset(7) |
2007-05-08 12:35:29.1234567 +12:15 |
B. 获取当前系统日期和时间
下面的示例说明如何使用返回当前日期和时间的 SQL Server 系统函数。
SELECT SYSDATETIME() AS 'SYSDATETIME'
--Results
--SYSDATETIME
--2007-10-22 14:10:41.7984554
--(1 row(s) affected)
SELECT SYSDATETIMEOFFSET() AS 'SYSDATETIMEOFFSET'
--Results
--SYSDATETIMEOFFSET
--2007-10-22 14:11:34.2607172 -0
--(1 row(s) affected)
SELECT SYSUTCDATETIME() AS 'SYSUTCDATETIME'
--Results
--SYSUTCDATETIME
--2007-10-22 21:12:11.7069470
--(1 row(s) affected)
SELECT CURRENT_TIMESTAMP AS 'CURRENT_TIMESTAMP'
--Results
--CURRENT_TIMESTAMP
-------------------------
--2007-10-22 14:12:33.320
--(1 row(s) affected)
SELECT GETDATE() AS 'GETDATE'
--Results
--GETDATE
--2007-10-22 14:13:57.943
--(1 row(s) affected)
SELECT GETUTCDATE() AS 'GETUTCDATE'
--Results
--GETUTCDATE
--2007-10-22 21:14:35.657
--(1 row(s) affected)
C. 搜索一天中的所有 datetime2 值
- 下面的示例说明如何搜索一天中的所有日期和时间值。
-- Create a table that contains with the following dates:
-- The last date-time in 2005-04-06, '2005-04-06 23:59:59.9999999'
-- The first date-time in 2005-04-07, '2005-04-07 00:00:00.0000000'
-- The last date-time in 2005-04-07, '2005-04-07 23:59:59.9999999'
-- The first date-time in 2005-04-08, '2005-04-08 00:00:00.0000000'
CREATE TABLE #Search
(
MyDate datetime2
);
INSERT INTO #Search(MyDate)VALUES('2005-04-06 23:59:59.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-07 00:00:00.0000000');
INSERT INTO #Search(MyDate)VALUES('2005-04-07 23:59:59.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-08 00:00:00.0000000');
-- The following four SELECT statements show different ways to find
-- only the two rows that contain 2005-04-07 dates.
--Use CONVERT.
SELECT MyDate
FROM #Search
WHERE CONVERT(date,MyDate) = '2005-04-07';
--Use >= and <=.
SELECT MyDate
FROM #Search
WHERE MyDate >= '2005-04-07 00:00:00.0000000'
AND MyDate <= '2005-04-07 23:59:59.9999999';
--Use > and <.
SELECT MyDate
FROM #Search
WHERE MyDate > '2005-04-06 23:59:59.9999999'
AND MyDate < '2005-04-08 00:00:00.0000000';
--Use BETWEEN AND.
SELECT MyDate
FROM #Search
WHERE MyDate BETWEEN '2005-04-07 00:00:00.0000000'
AND '2005-04-07 23:59:59.9999999';
DROP TABLE #Search
GO
D. 搜索一天中的某个时间段
下面的示例说明了如何搜索具有时间值的日期以查找时间范围。
-- Create a table called Search and insert
-- five different time values for the same
-- date.
CREATE TABLE #Search
(
MyDate datetime2
);
INSERT INTO #Search(MyDate)VALUES('2005-04-06 08:59:59.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-06 09:00:00.0000000');
INSERT INTO #Search(MyDate)VALUES('2005-04-06 09:00:00.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-06 16:59:59.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-06 17:00:00.0000000');
-- The following SELECT statements show different ways
-- to search for dates that have time values to find a
-- time range.
--Using CONVERT with time (0) to ignore fractional seconds
SELECT MyDate
FROM #Search
WHERE CONVERT(time(0),MyDate) = '09:00:00';
--Returns two rows (ignores fractional seconds):
--2005-04-06 08:59:59.9999999
--2005-04-06 09:00:00.0000000
--Using CONVERT with time (7) to include fractional seconds
SELECT MyDate
FROM #Search
WHERE CONVERT(time(7),MyDate) = '09:00:00';
--Returns one row (matches fractional seconds):
--2005-04-06 09:00:00.0000000
--Each of the SELECT statements below use CONVERT
--to find all times in an eight-hour period.
--Use CONVERT with >= and <=.
SELECT MyDate
FROM #Search
WHERE CONVERT(time(7),MyDate) >= '09:00:00.0000000'
AND CONVERT(time(7),MyDate) <= '16:59:59.9999999'
--Use CONVERT with > and <.
SELECT MyDate
FROM #Search
WHERE CONVERT(time(7),MyDate) > '08:59:59.9999999'
AND CONVERT(time(7),MyDate) < '17:00:00.0000000';
--Use CONVERT with BETWEEN AND
SELECT MyDate
FROM #Search
WHERE CONVERT(time(7),MyDate) BETWEEN '09:00:00.0000000'
AND '16:59:59.9999999';
DROP TABLE #Search
GO
E. 使用 DATEPART 和 DATEADD 查找日期部分的第一天和最后一天
下面的示例说明如何返回 datepart 的第一天或最后一天。
-- When several months, quarters, or years are added to the base
-- year,1900-01-01, the returned day is the first day of a month.
-- To calculate the last day of the current month, you need to
--
-- 1. Find the difference in months between today and the base
-- year (0 ='1900-01-01'). This is accomplished by
--
-- DATEDIFF(month, 0, SYSDATETIME())
-- 2. Add that number of months plus 1 to the base year (0)
-- to obtain the first day of the next month.
-- DATEADD(month, DATEDIFF(month, 0, SYSDATETIME()) + 1, 0)
-- 3. Subtract 1 day.
--
--Find the first day of the current month.
SELECT DATEADD(month, DATEDIFF(month, 0, SYSDATETIME()), 0);
--Find the last day of the current month.
SELECT DATEADD(month, DATEDIFF(month, 0, SYSDATETIME()) + 1, 0) - 1;
-- Find the first day of the current quarter.
SELECT DATEADD(quarter, DATEDIFF(quarter, 0, SYSDATETIME()), 0);
-- Find the last day of the current quarter.
SELECT DATEADD(quarter, DATEDIFF(quarter, -1, SYSDATETIME()), -1);
-- Find the first day of the current year.
SELECT DATEADD(year, DATEDIFF(year, 0, SYSDATETIME()), 0);
-- Find the last day of the current year.
SELECT DATEADD(year, DATEDIFF(year, -1, SYSDATETIME()), -1);
F. 将用户定义的日期部分参数用于 DATEADD、DATEDIFF、DATENAME 和 DATEPART
下面的示例创建一个用户定义的标量函数，该函数将一个常量添加到 datetime2 值的任意部分。
USE tempdb
GO
CREATE FUNCTION UserDateadd
(
@datepart nvarchar(11)
,@number int
,@date datetime2
)
RETURNS datetime2
AS
BEGIN
DECLARE @Return datetime2
SELECT @Return = CASE @datepart
WHEN 'year' THEN DATEADD(year,@number,@date)
WHEN 'yy' THEN DATEADD(year,@number,@date)
WHEN 'yyyy' THEN DATEADD(year,@number,@date)
WHEN 'quarter' THEN DATEADD(quarter,@number,@date)
WHEN 'qq' THEN DATEADD(quarter,@number,@date)
WHEN 'month' THEN DATEADD(month,@number,@date)
WHEN 'mm' THEN DATEADD(month,@number,@date)
WHEN 'm' THEN DATEADD(month,@number,@date)
WHEN 'dayofyear' THEN DATEADD(dayofyear,@number,@date)
WHEN 'dy' THEN DATEADD(dayofyear,@number,@date)
WHEN 'y' THEN DATEADD(dayofyear,@number,@date)
WHEN 'day' THEN DATEADD(day,@number,@date)
WHEN 'dd' THEN DATEADD(day,@number,@date)
WHEN 'd' THEN DATEADD(day,@number,@date)
WHEN 'week' THEN DATEADD(week,@number,@date)
WHEN 'wk' THEN DATEADD(week,@number,@date)
WHEN 'ww' THEN DATEADD(week,@number,@date)
WHEN 'weekday' THEN DATEADD(weekday,@number,@date)
WHEN 'wk' THEN DATEADD(weekday,@number,@date)
WHEN 'w' THEN DATEADD(weekday,@number,@date)
WHEN 'hour' THEN DATEADD(hour,@number,@date)
WHEN 'hh' THEN DATEADD(hour,@number,@date)
WHEN 'minute' THEN DATEADD(minute,@number,@date)
WHEN 'mi' THEN DATEADD(minute,@number,@date)
WHEN 'n' THEN DATEADD(minute,@number,@date)
WHEN 'second' THEN DATEADD(second,@number,@date)
WHEN 'ss' THEN DATEADD(second,@number,@date)
WHEN 's' THEN DATEADD(second,@number,@date)
WHEN 'millisecond' THEN DATEADD(millisecond,@number,@date)
WHEN 'ms' THEN DATEADD(millisecond,@number,@date)
WHEN 'microsecond' THEN DATEADD(microsecond,@number,@date)
WHEN 'mcs' THEN DATEADD(microsecond,@number,@date)
WHEN 'nanosecond' THEN DATEADD(nanosecond,@number,@date)
WHEN 'ns' THEN DATEADD(nanosecond,@number,@date)
END
return @Return
END
GO
SELECT GetDate()
SELECT dbo.UserDateadd('year', 1, GetDate())
GO
G. 使用 DATEPART 按日期的各个部分进行分组
下面的示例使用 AdventureWorks 示例数据库。使用 DATEPART 按工作日、月、年以及年/月/工作日来对总销售额进行分组。
USE AdventureWorks
GO
SELECT SUM(TotalDue) AS 'Total Sales'
,DATEPART(year,OrderDate)AS 'By Year'
,DATEPART(month,OrderDate) AS 'By Month'
,DATEPART(weekday,OrderDate) AS 'By Weekday'
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) IN('2003','2004')
GROUP BY GROUPING SETS
(
(DATEPART(year,OrderDate))
,(DATEPART(month,OrderDate))
,(DATEPART(weekday,OrderDate))
,(
DATEPART(year,OrderDate)
,DATEPART(month,OrderDate),
DATEPART(weekday,OrderDate))
)
ORDER BY DATEPART(year,OrderDate)
,DATEPART(month,OrderDate)
,DATEPART(weekday,OrderDate)