日期和时间数据类型及函数 (Transact-SQL)Date and Time Data Types and Functions (Transact-SQL)

适用于: 是SQL Server是Azure SQL 数据库是Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse noParallel Data Warehouse

本主题的各节涵盖了所有 Transact-SQLTransact-SQL 日期和时间数据类型及函数。The sections in this topic cover all Transact-SQLTransact-SQL date and time data types and functions.

日期和时间数据类型Date and Time data types

下表列出了 Transact-SQLTransact-SQL 的日期和时间数据类型:The Transact-SQLTransact-SQL date and time data types are listed in the following table:

数据类型Data type “格式”Format 范围Range 精确度Accuracy 存储大小(字节)Storage size (bytes) 用户定义的秒的小数部分精度User-defined fractional second precision 时区偏移量Time zone offset
timetime hh:mm:ss[.nnnnnnn]hh:mm:ss[.nnnnnnn] 00:00:00.0000000 到 23:59:59.999999900:00:00.0000000 through 23:59:59.9999999 100 纳秒100 nanoseconds 3 到 53 to 5 Yes No
datedate YYYY-MM-DDYYYY-MM-DD 0001-01-01 到 31.12.990001-01-01 through 9999-12-31 1 天1 day 33 No No
smalldatetimesmalldatetime YYYY-MM-DD hh:mm:ssYYYY-MM-DD hh:mm:ss 1900-01-01 到 2079-06-061900-01-01 through 2079-06-06 1 分钟1 minute 44 No No
datetimedatetime YYYY-MM-DD hh:mm:ss[.nnn]YYYY-MM-DD hh:mm:ss[.nnn] 1753-01-01 到 9999-12-311753-01-01 through 9999-12-31 0.00333 秒0.00333 second 88 No No
datetime2datetime2 YYYY-MM-DD hh:mm:ss[.nnnnnnn]YYYY-MM-DD hh:mm:ss[.nnnnnnn] 0001-01-01 00:00:00.0000000 到 9999-12-31 23:59:59.99999990001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 100 纳秒100 nanoseconds 6 到 86 to 8 Yes No
datetimeoffsetdatetimeoffset YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mmYYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm 0001-01-01 00:00:00.0000000 到 9999-12-31 23:59:59.9999999(以 UTC 时间表示)0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC) 100 纳秒100 nanoseconds 8 到 108 to 10 Yes Yes

备注

Transact-SQLTransact-SQL rowversion 数据类型不是日期或时间数据类型。The Transact-SQLTransact-SQL rowversion data type is not a date or time data type. timestamp 是 rowversion 的已弃用同义词。timestamp is a deprecated synonym for rowversion.

日期和时间函数Date and Time functions

以下各表列出了 Transact-SQLTransact-SQL 日期和时间函数。The following tables list the Transact-SQLTransact-SQL date and time functions. 有关确定性的详细信息,请参阅确定性函数和非确定性函数See Deterministic and Nondeterministic Functions for more information about determinism.

返回系统日期和时间值的函数Function That Return System Date and Time Values

Transact-SQLTransact-SQL 从运行 SQL ServerSQL Server 实例的计算机的操作系统派生所有系统日期和时间值。derives all system date and time values from the operating system of the computer on which the instance of SQL ServerSQL Server runs.

精度较高的系统日期和时间函数Higher-Precision System Date and Time Functions

SQL Server 2017SQL Server 2017 使用 GetSystemTimeAsFileTime() Windows API 派生日期和时间值。derives the date and time values through use of the GetSystemTimeAsFileTime() Windows API. 精确度取决于运行 SQL ServerSQL Server 实例的计算机硬件和 Windows 版本。The accuracy depends on the computer hardware and version of Windows on which the instance of SQL ServerSQL Server running. 此 API 的精确度固定为 100 纳秒。This API has a precision fixed at 100 nanoseconds. 可使用 GetSystemTimeAdjustment() Windows API 确定该精确度。Use the GetSystemTimeAdjustment() Windows API to determine the accuracy.

函数Function 语法Syntax 返回值Return value 返回数据类型Return data type 确定性Determinism
SYSDATETIMESYSDATETIME SYSDATETIME ()SYSDATETIME () 返回包含计算机的日期和时间的 datetime2(7) 值,SQL ServerSQL Server 的实例在该计算机上运行。Returns a datetime2(7) value containing the date and time of the computer on which the instance of SQL ServerSQL Server runs. 返回值不包括时区偏移量。The returned value does not include the time zone offset. datetime2(7)datetime2(7) 不具有确定性Nondeterministic
SYSDATETIMEOFFSETSYSDATETIMEOFFSET SYSDATETIMEOFFSET ( )SYSDATETIMEOFFSET ( ) 返回包含计算机的日期和时间的 datetimeoffset(7) 值,SQL ServerSQL Server 的实例在该计算机上运行。Returns a datetimeoffset(7) value containing the date and time of the computer on which the instance of SQL ServerSQL Server runs. 返回值包括时区偏移量。The returned value includes the time zone offset. datetimeoffset(7)datetimeoffset(7) 不具有确定性Nondeterministic
SYSUTCDATETIMESYSUTCDATETIME SYSUTCDATETIME ( )SYSUTCDATETIME ( ) 返回包含计算机的日期和时间的 datetime2(7) 值,SQL ServerSQL Server 的实例正在该计算机上运行。Returns a datetime2(7) value containing the date and time of the computer on which the instance of SQL ServerSQL Server is running. 该函数返回日期和时间作为 UTC 时间(协调世界时)。The function returns the date and time values as UTC time (Coordinated Universal Time). datetime2(7)datetime2(7) 不具有确定性Nondeterministic

精度较低的系统日期和时间函数Lower-Precision System Date and Time Functions

函数Function 语法Syntax 返回值Return value 返回数据类型Return data type 确定性Determinism
CURRENT_TIMESTAMPCURRENT_TIMESTAMP CURRENT_TIMESTAMPCURRENT_TIMESTAMP 返回包含计算机的日期和时间的 datetime 值,SQL ServerSQL Server 的实例在该计算机上运行。Returns a datetime value containing the date and time of the computer on which the instance of SQL ServerSQL Server runs. 返回值不包括时区偏移量。The returned value does not include the time zone offset. datetimedatetime 不具有确定性Nondeterministic
GETDATEGETDATE GETDATE ( )GETDATE ( ) 返回包含计算机的日期和时间的 datetime 值,SQL ServerSQL Server 的实例在该计算机上运行。Returns a datetime value containing the date and time of the computer on which the instance of SQL ServerSQL Server runs. 返回值不包括时区偏移量。The returned value does not include the time zone offset. datetimedatetime 不具有确定性Nondeterministic
GETUTCDATEGETUTCDATE GETUTCDATE ( )GETUTCDATE ( ) 返回包含计算机的日期和时间的 datetime 值,SQL ServerSQL Server 的实例在该计算机上运行。Returns a datetime value containing the date and time of the computer on which the instance of SQL ServerSQL Server runs. 该函数返回日期和时间作为 UTC 时间(协调世界时)。The function returns the date and time values as UTC time (Coordinated Universal Time). datetimedatetime 不具有确定性Nondeterministic

返回日期和时间部分的函数Functions That Return Date and Time Parts

函数Function 语法Syntax 返回值Return value 返回数据类型Return data type 确定性Determinism
DATENAMEDATENAME DATENAME ( datepart , date )DATENAME ( datepart , date ) 返回表示指定 date 的指定 datepart 的字符串。Returns a character string representing the specified datepart of the specified date. nvarcharnvarchar 不具有确定性Nondeterministic
DATEPARTDATEPART DATEPART ( datepart , date )DATEPART ( datepart , date ) 返回表示指定 date 的指定 datepart 的整数。Returns an integer representing the specified datepart of the specified date. intint 不具有确定性Nondeterministic
DAYDAY DAY ( date )DAY ( date ) 返回表示指定 date 的“日”部分的整数。Returns an integer representing the day part of the specified date. intint 具有确定性Deterministic
MONTHMONTH MONTH ( date )MONTH ( date ) 返回表示指定 date 的“月”部分的整数。Returns an integer representing the month part of a specified date. intint 具有确定性Deterministic
YEARYEAR YEAR ( date )YEAR ( date ) 返回表示指定 date 的“年”部分的整数。Returns an integer representing the year part of a specified date. intint 具有确定性Deterministic

从相应部分返回日期和时间值的函数Functions That Return Date and Time Values from Their Parts

函数Function 语法Syntax 返回值Return value 返回数据类型Return data type 确定性Determinism
DATEFROMPARTSDATEFROMPARTS DATEFROMPARTS ( year, month, day )DATEFROMPARTS ( year, month, day ) 返回表示指定年、月、日的 date 值。Returns a date value for the specified year, month, and day. datedate 具有确定性Deterministic
DATETIME2FROMPARTSDATETIME2FROMPARTS DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision)DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision) 对指定的日期和时间返回 datetime2 值(具有指定精度)。Returns a datetime2 value for the specified date and time, with the specified precision. datetime2( precision )datetime2( precision ) 具有确定性Deterministic
DATETIMEFROMPARTSDATETIMEFROMPARTS DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds)DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds) 为指定的日期和时间返回 datetime 值。Returns a datetime value for the specified date and time. datetimedatetime 具有确定性Deterministic
DATETIMEOFFSETFROMPARTSDATETIMEOFFSETFROMPARTS DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision)DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision) 对指定的日期和时间返回 datetimeoffset 值(具有指定的偏移量和精度)。Returns a datetimeoffset value for the specified date and time, with the specified offsets and precision. datetimeoffset( precision )datetimeoffset( precision ) 具有确定性Deterministic
SMALLDATETIMEFROMPARTSSMALLDATETIMEFROMPARTS SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute ) 为指定的日期和时间返回 smalldatetime 值。Returns a smalldatetime value for the specified date and time. smalldatetimesmalldatetime 具有确定性Deterministic
TIMEFROMPARTSTIMEFROMPARTS TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )TIMEFROMPARTS ( hour, minute, seconds, fractions, precision ) 对指定的时间返回 time 值(具有指定精度)。Returns a time value for the specified time, with the specified precision. time( precision )time( precision ) 具有确定性Deterministic

返回日期和时间差异值的函数Functions That Return Date and Time Difference Values

函数Function 语法Syntax 返回值Return value 返回数据类型Return data type 确定性Determinism
DATEDIFFDATEDIFF DATEDIFF ( datepart , startdate , enddate )DATEDIFF ( datepart , startdate , enddate ) 返回两个指定日期之间所跨的日期或时间 datepart 边界数。Returns the number of date or time datepart boundaries, crossed between two specified dates. intint 具有确定性Deterministic
DATEDIFF_BIGDATEDIFF_BIG DATEDIFF_BIG ( datepart , startdate , enddate )DATEDIFF_BIG ( datepart , startdate , enddate ) 返回两个指定日期之间所跨的日期或时间 datepart 边界数。Returns the number of date or time datepart boundaries, crossed between two specified dates. bigintbigint 具有确定性Deterministic

修改日期和时间值的函数Functions That Modify Date and Time Values

函数Function 语法Syntax 返回值Return value 返回数据类型Return data type 确定性Determinism
DATEADDDATEADD DATEADD (datepart , number , date )DATEADD (datepart , number , date ) 通过将一个时间间隔与指定 date 的指定 datepart 相加,返回一个新的 datetime 值。Returns a new datetime value by adding an interval to the specified datepart of the specified date. date 参数的数据类型The data type of the date argument 具有确定性Deterministic
EOMONTHEOMONTH EOMONTH ( start_date [, month_to_add ] )EOMONTH ( start_date [, month_to_add ] ) 返回包含指定日期的月份的最后一天(具有可选偏移量)。Returns the last day of the month containing the specified date, with an optional offset. 返回类型为 start_date 参数类型或 date 数据类型。Return type is the type of the start_date argument, or alternately, the date data type. 具有确定性Deterministic
SWITCHOFFSETSWITCHOFFSET SWITCHOFFSET (DATETIMEOFFSET , time_zone)SWITCHOFFSET (DATETIMEOFFSET , time_zone) SWITCHOFFSET 更改 DATETIMEOFFSET 值的时区偏移量并保留 UTC 值。SWITCHOFFSET changes the time zone offset of a DATETIMEOFFSET value, and preserves the UTC value. 具有 DATETIMEOFFSET 小数精度的 datetimeoffsetdatetimeoffset with the fractional precision of the DATETIMEOFFSET 具有确定性Deterministic
TODATETIMEOFFSETTODATETIMEOFFSET TODATETIMEOFFSET (expression , time_zone)TODATETIMEOFFSET (expression , time_zone) TODATETIMEOFFSET 将 datetime2 值转换为 datetimeoffset 值。TODATETIMEOFFSET transforms a datetime2 value into a datetimeoffset value. TODATETIMEOFFSET 针对指定 time_zone 解释本地时间的 datetime2 值。TODATETIMEOFFSET interprets the datetime2 value in local time, for the specified time_zone. 具有 datetime 参数小数精度的 datetimeoffsetdatetimeoffset with the fractional precision of the datetime argument 具有确定性Deterministic

设置或返回会话格式函数的函数Functions That Set or Return Session Format Functions

函数Function 语法Syntax 返回值Return value 返回数据类型Return data type 确定性Determinism
@@DATEFIRST@@DATEFIRST @@DATEFIRST@@DATEFIRST 返回对会话进行 SET DATEFIRST 操作所得结果的当前值。Returns the current value, for the session, of SET DATEFIRST. tinyinttinyint 不具有确定性Nondeterministic
SET DATEFIRSTSET DATEFIRST SET DATEFIRST { number | @number_var }SET DATEFIRST { number | *@***number_var } 将一周的第一天设置为从 1 到 7 的一个数字。Sets the first day of the week to a number from 1 through 7. 不适用Not applicable 不适用Not applicable
SET DATEFORMATSET DATEFORMAT SET DATEFORMAT { format | @format_var }SET DATEFORMAT { format | @format_var } 设置用于输入 datetime 或 smalldatetime 数据的日期各部分(月/日/年)的顺序。Sets the order of the dateparts (month/day/year) for entering datetime or smalldatetime data. 不适用Not applicable 不适用Not applicable
@@LANGUAGE@@LANGUAGE @@LANGUAGE@@LANGUAGE 返回当前所用语言的名称。Returns the name of the language in current used. @@LANGUAGE 不是日期或时间函数。@@LANGUAGE is not a date or time function. 但是,语言设置会影响日期函数的输出。However, the language setting can affect the output of date functions. 不适用Not applicable 不适用Not applicable
SET LANGUAGESET LANGUAGE SET LANGUAGE { [ N ] 'language' | *@***language_var }SET LANGUAGE { [ N ] 'language' | *@***language_var } 设置会话和系统消息的语言环境。Sets the language environment for the session and system messages. SET LANGUAGE 不是日期或时间函数。SET LANGUAGE is not a date or time function. 但是,语言设置会影响日期函数的输出。However, the language setting affects the output of date functions. 不适用Not applicable 不适用Not applicable
sp_helplanguagesp_helplanguage sp_helplanguage [ [ @language = ] 'language' ]sp_helplanguage [ [ @language = ] 'language' ] 返回有关所有支持语言的日期格式的信息。Returns information about date formats of all supported languages. sp_helplanguage 不是日期或时间存储过程。sp_helplanguage is not a date or time stored procedure. 但是,语言设置会影响日期函数的输出。However, the language setting affects the output of date functions. 不适用Not applicable 不适用Not applicable

验证日期和时间值的函数Functions That Validate Date and Time Values

函数Function 语法Syntax 返回值Return value 返回数据类型Return data type 确定性Determinism
ISDATEISDATE ISDATE ( expression )ISDATE ( expression ) 确定 datetime 或 smalldatetime 输入表达式是否为有效的日期或时间值。Determines whether a datetime or smalldatetime input expression has a valid date or time value. intint 在指定了 CONVERT 样式参数且样式不等于 0、100、9 或 109 时,ISDATE 只有在与 CONVERT 函数一起使用时才是确定的。ISDATE is deterministic only used with the CONVERT function, when the CONVERT style parameter is specified, and when style is not equal to 0, 100, 9, or 109.

日期和时间相关主题Date and time-related topics

主题Topic 描述Description
CAST 和 CONVERT (Transact-SQL)CAST and CONVERT (Transact-SQL) 提供有关在日期和时间值与字符串文字及其他日期和时间格式之间进行相互转换的信息。Provides information about the conversion of date and time values to and from string literals, and other date and time formats.
编写国际化 Transact-SQL 语句Write International Transact-SQL Statements 提供使用 Transact-SQLTransact-SQL 语句的数据库和数据库应用程序在不同语言之间的可移植性准则,或支持多种语言的数据库和数据库应用程序的可移植性准则。Provides guidelines for portability of databases and database applications that use Transact-SQLTransact-SQL statements from one language to another, or that support multiple languages.
ODBC 标量函数 (Transact-SQL)ODBC Scalar Functions (Transact-SQL) 提供有关可在 Transact-SQLTransact-SQL 语句中使用的 ODBC 标量函数的信息。Provides information about ODBC scalar functions available for use in Transact-SQLTransact-SQL statements. 这包括 ODBC 日期和时间函数。This includes ODBC date and time functions.
AT TIME ZONE (Transact-SQL)AT TIME ZONE (Transact-SQL) 提供时区转换。Provides time zone conversion.

另请参阅See also

函数Functions
数据类型 (Transact-SQL)Data Types (Transact-SQL)