datetime (Transact-SQL)datetime (Transact-SQL)

适用对象:是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

用于定义一个与采用 24 小时制并带有秒小数部分的一日内时间相组合的日期。Defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock.

备注

为新的工作使用 time、date、datetime2 和 datetimeoffset 数据类型 。Use the time, date, datetime2 and datetimeoffset data types for new work. 这些类型符合 SQL 标准。These types align with the SQL Standard. 它们更易于移植。They are more portable. time、datetime2 和 datetimeoffset 提供更高精度的秒数 。time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset 为全局部署的应用程序提供时区支持 。datetimeoffset provides time zone support for globally deployed applications.

datetime 说明datetime Description

propertiesProperty Value
语法Syntax datetimedatetime
使用情况Usage DECLARE @MyDatetime datetime DECLARE @MyDatetime datetime

CREATE TABLE Table1 (Column1 datetime )CREATE TABLE Table1 ( Column1 datetime )
默认的字符串文字格式Default string literal formats

(用于下级客户端)(used for down-level client)
不适用Not applicable
日期范围Date range 1753 年 1 月 1 日到 9999 年 12 月 31 日January 1, 1753, through December 31, 9999
时间范围Time range 00:00:00 到 23:59:59.99700:00:00 through 23:59:59.997
时区偏移量范围Time zone offset range None
各元素的范围Element ranges YYYY 是表示年份的四位数字,范围为 1753 到 9999。YYYY is four digits from 1753 through 9999 that represent a year.

MM 是表示指定年份中的月份的两位数字,范围为 01 到 12。MM is two digits, ranging from 01 to 12, that represent a month in the specified year.

DD 是表示指定月份中的某一天的两位数字,范围为 01 到 31(最高值取决于相应月份)。DD is two digits, ranging from 01 to 31 depending on the month, that represent a day of the specified month.

hh 是表示小时的两位数字,范围为 00 到 23。hh is two digits, ranging from 00 to 23, that represent the hour.

mm 是表示分钟的两位数字,范围为 00 到 59。mm is two digits, ranging from 00 to 59, that represent the minute.

ss 是表示秒钟的两位数字,范围为 00 到 59。ss is two digits, ranging from 00 to 59, that represent the second.

n* 为一个 0 到 3 位的数字,范围为 0 到 999,表示秒的小数部分。n* is zero to three digits, ranging from 0 to 999, that represent the fractional seconds.
字符长度Character length 最低 19 位到最高 23 位19 positions minimum to 23 maximum
存储大小Storage size 8 字节8 bytes
精确度Accuracy 舍入到 .000、.003 或 .007 秒三个增量。Rounded to increments of .000, .003, or .007 seconds
默认值Default value 1900-01-01 00:00:001900-01-01 00:00:00
日历Calendar 公历(包括完整的年份范围。)Gregorian (Does include the complete range of years.)
用户定义的秒的小数部分精度User-defined fractional second precision No
时区偏移量感知和保留Time zone offset aware and preservation No
夏时制感知Daylight saving aware No

datetime 支持的字符串文字格式Supported String Literal Formats for datetime

以下各表列出了 datetime 支持的字符串文字格式 。The following tables list the supported string literal formats for datetime. datetime 字符串文字位于单引号 (') 中,例如 'string_literaL',但 ODBC 除外 。Except for ODBC, datetime string literals are in single quotation marks ('), for example, 'string_literaL'. 如果环境不是 us_english,则字符串文字应采用 N'string_literaL' 格式 。If the environment isn't us_english, the string literals should be in the format N'string_literaL'.

NumericNumeric 说明Description
日期格式:Date formats:

[0]4/15/[19]96 -- (mdy)[0]4/15/[19]96 -- (mdy)

[0]4-15-[19]96 -- (mdy)[0]4-15-[19]96 -- (mdy)

[0]4.15.[19]96 -- (mdy)[0]4.15.[19]96 -- (mdy)

[0]4/[19]96/15 -- (myd)[0]4/[19]96/15 -- (myd)

15/[0]4/[19]96 -- (dmy)15/[0]4/[19]96 -- (dmy)

15/[19]96/[0]4 -- (dym)15/[19]96/[0]4 -- (dym)

[19]96/15/[0]4 -- (ydm)[19]96/15/[0]4 -- (ydm)

[19]96/[0]4/15 -- (ymd)[19]96/[0]4/15 -- (ymd)

时间格式:Time formats:

14:3014:30

14:30[:20:999]14:30[:20:999]

14:30[:20.9]14:30[:20.9]

4am4am

4 PM4 PM
您可以指定日期数据,其中月份也通过数值指定。You can specify date data with a numeric month specified. 例如 5/20/97 表示 1997 年 5 月 20 日。For example, 5/20/97 represents the twentieth day of May 1997. 当使用数值日期格式时,请在使用正斜杠符号 (/)、连字符 (-) 或句点 (.) 作为分隔符的字符串中指定年、月、日。When you use numeric date format, specify the month, day, and year in a string that uses slash marks (/), hyphens (-), or periods (.) as separators. 此字符串必须采用以下格式:This string must appear in the following form:

数字 分隔符 数字 分隔符 数字 [时间] [时间]number separator number separator number [time] [time]



当语言设置为 us_english 时,默认的日期顺序是 mdy 。When the language is set to us_english, the default order for the date is mdy. 可以使用 SET DATEFORMAT 语句更改日期顺序。You can change the date order by using the SET DATEFORMAT statement.

SET DATEFORMAT 的设置决定了如何解释日期值。The setting for SET DATEFORMAT determines how date values are interpreted. 如果顺序不符合设置,这些值不会被解释为日期。If the order doesn't match the setting, the values aren't interpreted as dates. 无序的日期可能会被错误解释成超出范围或使用错误的值。Out-of-order dates may be misinterpreted as out of range or with wrong values. 例如,12/10/08 可以解释成六个不同的日期,具体解释为哪一日期取决于 DATEFORMAT 的设置。For example, 12/10/08 can be interpreted as one of six dates, depending on the DATEFORMAT setting. 四位数字的年份被解释为年。A four-part year is interpreted as the year.
字母Alphabetical 说明Description
Apr[il] [15][,] 1996Apr[il] [15][,] 1996

Apr[il] 15[,] [19]96Apr[il] 15[,] [19]96

Apr[il] 1996 [15]Apr[il] 1996 [15]

[15] Apr[il][,] 1996[15] Apr[il][,] 1996

15 Apr[il][,][19]9615 Apr[il][,][19]96

15 [19]96 apr[il]15 [19]96 apr[il]

[15] 1996 apr[il][15] 1996 apr[il]

1996 APR[IL] [15]1996 APR[IL] [15]

1996 [15] APR[IL]1996 [15] APR[IL]
您可以指定一个日期数据,其中使用完整的月份名称来指定月份。You can specify date data with a month specified as the full month name. 例如,月份用英语 April 或使用其缩写 Apr 指定;逗号是可选的,且忽略大小写。For example, April or the month abbreviation of Apr specified in the current language; commas are optional and capitalization is ignored.

下面是使用字母日期格式的一些准则:Here are some guidelines for using alphabetical date formats:

1) 日期和时间数据要放在单引号 (') 内。1) Enclose the date and time data in single quotation marks ('). 对于英语以外的其他语言,使用 N'。For languages other than English, use N'

2) 方括号中的字符是可选的。2) Characters that are enclosed in brackets are optional.

3) 如果只指定年份的最后两位数字,则小于配置 two digit year cutoff(两位数年份截止)服务器配置选项配置选项值最后两位数字的值与截止年份处于同一个世纪。3) If you specify only the last two digits of the year, values less than the last two digits of the value of the Configure the two digit year cutoff Server Configuration Option configuration option are in the same century as the cutoff year. 大于或等于该选项值的值处于截止年份的上一个世纪。Values greater than or equal to the value of this option are in the century that comes before the cutoff year. 例如,如果“两位数年份截止”为 2050(默认值),则两位数年份 25 被解释为 2025 年,而 50 被解释为 1950 年 。For example, if two digit year cutoff is 2050 (default), 25 is interpreted as 2025 and 50 is interpreted as 1950. 为避免不确定性,请使用四位数年份。To avoid ambiguity, use four-digit years.

4) 如果没有指定日,则默认为当月第一天。4) If the day is missing, the first day of the month is supplied.



当按字母形式指定月份时,SET DATEFORMAT 会话设置不起作用。The SET DATEFORMAT session setting isn't applied when you specify the month in alphabetical form.
ISO 8601ISO 8601 说明Description
YYYY-MM-DDThh:mm:ss[.mmm]YYYY-MM-DDThh:mm:ss[.mmm]

YYYYMMDD[ hh:mm:ss[.mmm]]YYYYMMDD[ hh:mm:ss[.mmm]]
示例:Examples:

1) 2004-05-23T14:25:101) 2004-05-23T14:25:10

2) 2004-05-23T14:25:10.4872) 2004-05-23T14:25:10.487



若要使用 ISO 8601 格式,必须指定每个元素的格式,包括格式中显示的 T、冒号 (:)、句点 (.) 。To use the ISO 8601 format, you must specify each element in the format, including the T, the colons (:), and the period (.) that are shown in the format.

方括号表示秒小数部分是可选的。The brackets indicate that the fraction of second component is optional. 时间部分按 24 小时制指定。The time component is specified in the 24-hour format.

T 表示后面是 datetime 值的时间部分 。The T indicates the start of the time part of the datetime value.

使用 ISO 8601 格式的优点是它是一种国际标准,不会产生模糊的指定。The advantage in using the ISO 8601 format is that it is an international standard with unambiguous specification. 同时,此格式不受 SET DATEFORMAT 或 SET LANGUAGE 设置的影响。Also, this format isn't affected by the SET DATEFORMAT or SET LANGUAGE setting.
未分隔的Unseparated 说明Description
YYYYMMDD hh:mm:ss[.mmm]YYYYMMDD hh:mm:ss[.mmm]
ODBCODBC 说明Description
{ ts '1998-05-02 01:23:56.123' }{ ts '1998-05-02 01:23:56.123' }

{ d '1990-10-02' }{ d '1990-10-02' }

{ t '13:33:41' }{ t '13:33:41' }
ODBC API 用于定义转义序列以表示日期和时间值,ODBC 称之为时间戳数据。The ODBC API defines escape sequences to represent date and time values, which ODBC calls timestamp data. MicrosoftMicrosoft OLE DB provider for SQL ServerSQL Server 所支持的 OLE DB 语言定义 (DBGUID-SQL) 也支持这种 ODBC 时间戳格式。This ODBC timestamp format is also supported by the OLE DB language definition (DBGUID-SQL) supported by the MicrosoftMicrosoft OLE DB provider for SQL ServerSQL Server. 使用 ADO、OLE DB 和基于 ODBC 的 API 的应用程序可以使用这种 ODBC 时间戳格式来表示日期和时间。Applications that use the ADO, OLE DB, and ODBC-based APIs can use this ODBC timestamp format to represent dates and times.

ODBC 时间戳转义序列的格式为:{ literal_type 'constant_value' }:ODBC timestamp escape sequences are of the format: { literal_type 'constant_value' }:



- literal_type 指定转义序列的类型 。- literal_type specifies the type of the escape sequence. 时间戳具有三个 literal_type 说明符 :Timestamps have three literal_type specifiers:
1) d = 仅日期1) d = date only
2) t = 仅时间2) t = time only
3) ts = 时间戳(时间 + 日期)3) ts = timestamp (time + date)



- 'constant_value' 是转义序列的值 。- 'constant_value' is the value of the escape sequence. constant_value 必须遵循以下每种 literal_type 的格式 。constant_value must follow these formats for each literal_type.
d : yyyy-mm-ddd : yyyy-mm-dd
t : hh:mm:ss[.fff]t : hh:mm:ss[.fff]
ts : yyyy-mm-dd hh:mm:ss[.fff]ts : yyyy-mm-dd hh:mm:ss[.fff]

datetime 秒的小数部分精度的舍入Rounding of datetime Fractional Second Precision

如下表所示,将 datetime 值舍入到 .000、.003、或 .007 秒的增量 。datetime values are rounded to increments of .000, .003, or .007 seconds, as shown in the following table.

用户指定的值User-specified value 系统存储的值System stored value
01/01/98 23:59:59.99901/01/98 23:59:59.999 1998-01-02 00:00:00.0001998-01-02 00:00:00.000
01/01/98 23:59:59.99501/01/98 23:59:59.995

01/01/98 23:59:59.99601/01/98 23:59:59.996

01/01/98 23:59:59.99701/01/98 23:59:59.997

01/01/98 23:59:59.99801/01/98 23:59:59.998
1998-01-01 23:59:59.9971998-01-01 23:59:59.997
01/01/98 23:59:59.99201/01/98 23:59:59.992

01/01/98 23:59:59.99301/01/98 23:59:59.993

01/01/98 23:59:59.99401/01/98 23:59:59.994
1998-01-01 23:59:59.9931998-01-01 23:59:59.993
01/01/98 23:59:59.99001/01/98 23:59:59.990

01/01/98 23:59:59.99101/01/98 23:59:59.991
1998-01-01 23:59:59.9901998-01-01 23:59:59.990

对 ANSI 和 ISO 8601 的遵从性ANSI and ISO 8601 Compliance

datetime 不遵从 ANSI 或 ISO 8601 。datetime isn't ANSI or ISO 8601 compliant.

转换日期和时间数据Converting Date and Time Data

当转换为日期和时间数据类型时,SQL ServerSQL Server 将会拒绝它无法识别为日期或时间的所有值。When you convert to date and time data types, SQL ServerSQL Server rejects all values it can't recognize as dates or times. 有关对日期和时间数据使用 CAST 和 CONVERT 函数的信息,请参阅 CAST 和 CONVERT (Transact-SQL)For information about using the CAST and CONVERT functions with date and time data, see CAST and CONVERT (Transact-SQL).

将其他日期和时间类型转换为 datetime 数据类型Converting Other Date and Time Types to the datetime Data Type

本部分介绍其他日期和时间数据类型转换为 datetime 数据类型时会发生什么 。This section describes what occurs when other date and time data types are converted to the datetime data type.

从 date 转换时,会复制年、月和日 。When the conversion is from date, the year, month, and day are copied. 时间部分设置为 00:00:00.000。The time component is set to 00:00:00.000. 下面的代码显示将 date 值转换为 datetime 值的结果。The following code shows the results of converting a date value to a datetime value.

DECLARE @date date = '12-21-16';  
DECLARE @datetime datetime = @date;  
  
SELECT @datetime AS '@datetime', @date AS '@date';  
  
--Result  
--@datetime               @date  
------------------------- ----------  
--2016-12-21 00:00:00.000 2016-12-21  

从 time(n) 转换时,会复制时间部分,日期部分设置为“1900-01-01” 。When the conversion is from time(n), the time component is copied, and the date component is set to '1900-01-01'. 当 time(n) 值的小数部分精度大于三位时,该值将被截断,以适合目标精度 。When the fractional precision of the time(n) value is greater than three digits, the value will be truncated to fit. 下面的示例显示了将 time(4) 值转换为 datetime 值的结果。The following example shows the results of converting a time(4) value to a datetime value.

DECLARE @time time(4) = '12:10:05.1237';  
DECLARE @datetime datetime = @time;  
  
SELECT @datetime AS '@datetime', @time AS '@time';  
  
--Result  
--@datetime               @time  
------------------------- -------------  
--1900-01-01 12:10:05.123 12:10:05.1237  

从 smalldatetime 转换时,会复制小时和分钟。 When the conversion is from smalldatetime, the hours and minutes are copied. 秒和秒的小数部分设置为 0。The seconds and fractional seconds are set to 0. 下面的代码显示将 smalldatetime 值转换为 datetime 值的结果。The following code shows the results of converting a smalldatetime value to a datetime value.

DECLARE @smalldatetime smalldatetime = '12-01-16 12:32';  
DECLARE @datetime datetime = @smalldatetime;  
  
SELECT @datetime AS '@datetime', @smalldatetime AS '@smalldatetime';  
  
--Result  
--@datetime               @smalldatetime  
------------------------- -----------------------  
--2016-12-01 12:32:00.000 2016-12-01 12:32:00  

从 datetimeoffset(n) 转换时,会复制日期和时间部分 。When the conversion is from datetimeoffset(n), the date and time components are copied. 时区被截断。The time zone is truncated. 当 datetimeoffset(n) 值的小数部分精度大于三位时,该值将被截断 。When the fractional precision of the datetimeoffset(n) value is greater than three digits, the value will be truncated. 下面的示例显示了将 datetimeoffset(4) 值转换为 datetime 值的结果。The following example shows the results of converting a datetimeoffset(4) value to a datetime value.

DECLARE @datetimeoffset datetimeoffset(4) = '1968-10-23 12:45:37.1234 +10:0';  
DECLARE @datetime datetime = @datetimeoffset;  
  
SELECT @datetime AS '@datetime', @datetimeoffset AS '@datetimeoffset';  
  
--Result  
--@datetime               @datetimeoffset  
------------------------- ------------------------------  
--1968-10-23 12:45:37.123 1968-10-23 12:45:37.1237 +01:0   

从 datetime2(n) 转换时,会复制日期和时间 。When the conversion is from datetime2(n), the date and time are copied. 当 datetime2(n) 值的小数部分精度大于三位时,该值将被截断 。When the fractional precision of the datetime2(n) value is greater than three digits, the value will be truncated. 下面的示例显示了将 datetime2(4) 值转换为 datetime 值的结果。The following example shows the results of converting a datetime2(4) value to a datetime value.

DECLARE @datetime2 datetime2(4) = '1968-10-23 12:45:37.1237';  
DECLARE @datetime datetime = @datetime2;  
  
SELECT @datetime AS '@datetime', @datetime2 AS '@datetime2';  
  
--Result  
--@datetime               @datetime2  
------------------------- ------------------------  
--1968-10-23 12:45:37.123 1968-10-23 12:45:37.1237  

示例Examples

下例比较了将一个字符串分别转换为各种 date 和 time 数据类型时所产生的结果 。The following example compares the results of casting a string to each date and time data type.

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';  

下面是结果集:Here is the result set.

数据类型Data type 输出Output
timetime 12:35:29.12:35:29. 12345671234567
datedate 2007-05-082007-05-08
smalldatetimesmalldatetime 2007-05-08 12:35:002007-05-08 12:35:00
datetimedatetime 2007-05-08 12:35:29.1232007-05-08 12:35:29.123
datetime2datetime2 2007-05-08 12:35:29.2007-05-08 12:35:29. 12345671234567
datetimeoffsetdatetimeoffset 2007-05-08 12:35:29.1234567 +12:152007-05-08 12:35:29.1234567 +12:15

另请参阅See also

CAST 和 CONVERT (Transact-SQL)CAST and CONVERT (Transact-SQL)