[!div class=“nextstepaction”] Please help improve SQL Server docs!
The sections in this topic cover all Transact\-SQL date and time data types and functions. - Date and Time Data Types
- Date and Time Functions
- Function That Return System Date and Time Values
- Functions That Return Date and Time Parts
- Functions That Return Date and Time Values from Their Parts
- Functions That Return Date and Time Difference Values
- Functions That Modify Date and Time Values
- Functions That Set or Return Session Format Functions
- Functions That Validate Date and Time Values
- Date and Time–Related Topics
The Transact\-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 |
---|---|---|---|---|---|---|
time | hh:mm:ss[.nnnnnnn] | 00:00:00.0000000 through 23:59:59.9999999 | 100 nanoseconds | 3 to 5 | Yes | No |
date | YYYY-MM-DD | 0001-01-01 through 9999-12-31 | 1 day | 3 | No | No |
smalldatetime | YYYY-MM-DD hh:mm:ss | 1900-01-01 through 2079-06-06 | 1 minute | 4 | No | No |
datetime | YYYY-MM-DD hh:mm:ss[.nnn] | 1753-01-01 through 9999-12-31 | 0.00333 second | 8 | No | No |
datetime2 | YYYY-MM-DD hh:mm:ss[.nnnnnnn] | 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 | 100 nanoseconds | 6 to 8 | Yes | No |
datetimeoffset | YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm | 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC) | 100 nanoseconds | 8 to 10 | Yes | Yes |
[!NOTE]
The Transact\-SQL rowversion data type is not a date or time data type. timestamp is a deprecated synonym for rowversion.
The following tables list the Transact\-SQL date and time functions. See Deterministic and Nondeterministic Functions for more information about determinism.
Transact\-SQL derives all system date and time values from the operating system of the computer on which the instance of Transact\-SQL SQL Server runs.
SQL Server 2017 derives the date and time values through use of the GetSystemTimeAsFileTime() Windows API. The accuracy depends on the computer hardware and version of Windows on which the instance of SQL Server 2017 SQL Server running. This API has a precision fixed at 100 nanoseconds. Use the GetSystemTimeAdjustment() Windows API to determine the accuracy.
Function | Syntax | Return value | Return data type | Determinism |
---|---|---|---|---|
SYSDATETIME | SYSDATETIME () | Returns a datetime2(7) value containing the date and time of the computer on which the instance of [!INCLUDEssNoVersion] runs. The returned value does not include the time zone offset. | datetime2(7) | Nondeterministic |
SYSDATETIMEOFFSET | SYSDATETIMEOFFSET ( ) | Returns a datetimeoffset(7) value containing the date and time of the computer on which the instance of [!INCLUDEssNoVersion] runs. The returned value includes the time zone offset. | datetimeoffset(7) | Nondeterministic |
SYSUTCDATETIME | SYSUTCDATETIME ( ) | Returns a datetime2(7) value containing the date and time of the computer on which the instance of [!INCLUDEssNoVersion] is running. The function returns the date and time values as UTC time (Coordinated Universal Time). | datetime2(7) | Nondeterministic |
Function | Syntax | Return value | Return data type | Determinism |
---|---|---|---|---|
CURRENT_TIMESTAMP | CURRENT_TIMESTAMP | Returns a datetime value containing the date and time of the computer on which the instance of [!INCLUDEssNoVersion] runs. The returned value does not include the time zone offset. | datetime | Nondeterministic |
GETDATE | GETDATE ( ) | Returns a datetime value containing the date and time of the computer on which the instance of [!INCLUDEssNoVersion] runs. The returned value does not include the time zone offset. | datetime | Nondeterministic |
GETUTCDATE | GETUTCDATE ( ) | Returns a datetime value containing the date and time of the computer on which the instance of [!INCLUDEssNoVersion] runs. The function returns the date and time values as UTC time (Coordinated Universal Time). | datetime | Nondeterministic |
Function | Syntax | Return value | Return data type | Determinism |
---|---|---|---|---|
DATENAME | DATENAME ( datepart , date ) | Returns a character string representing the specified datepart of the specified date. | nvarchar | Nondeterministic |
DATEPART | DATEPART ( datepart , date ) | Returns an integer representing the specified datepart of the specified date. | int | Nondeterministic |
DAY | DAY ( date ) | Returns an integer representing the day part of the specified date. | int | Deterministic |
MONTH | MONTH ( date ) | Returns an integer representing the month part of a specified date. | int | Deterministic |
YEAR | YEAR ( date ) | Returns an integer representing the year part of a specified date. | int | Deterministic |
Function | Syntax | Return value | Return data type | Determinism |
---|---|---|---|---|
DATEFROMPARTS | DATEFROMPARTS ( year, month, day ) | Returns a date value for the specified year, month, and day. | date | Deterministic |
DATETIME2FROMPARTS | DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision) | Returns a datetime2 value for the specified date and time, with the specified precision. | datetime2( precision ) | Deterministic |
DATETIMEFROMPARTS | DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds) | Returns a datetime value for the specified date and time. | datetime | Deterministic |
DATETIMEOFFSETFROMPARTS | DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision) | Returns a datetimeoffset value for the specified date and time, with the specified offsets and precision. | datetimeoffset( precision ) | Deterministic |
SMALLDATETIMEFROMPARTS | SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute ) | Returns a smalldatetime value for the specified date and time. | smalldatetime | Deterministic |
TIMEFROMPARTS | TIMEFROMPARTS ( hour, minute, seconds, fractions, precision ) | Returns a time value for the specified time, with the specified precision. | time( precision ) | Deterministic |
Function | Syntax | Return value | Return data type | Determinism |
---|---|---|---|---|
DATEDIFF | DATEDIFF ( datepart , startdate , enddate ) | Returns the number of date or time datepart boundaries, crossed between two specified dates. | int | Deterministic |
DATEDIFF_BIG | DATEDIFF_BIG ( datepart , startdate , enddate ) | Returns the number of date or time datepart boundaries, crossed between two specified dates. | bigint | Deterministic |
Function | Syntax | Return value | Return data type | Determinism |
---|---|---|---|---|
DATEADD | DATEADD (datepart , number , date ) | Returns a new datetime value by adding an interval to the specified datepart of the specified date. | The data type of the date argument | Deterministic |
EOMONTH | EOMONTH ( start_date [, month_to_add ] ) | Returns the last day of the month containing the specified date, with an optional offset. | Return type is the type of the start_date argument, or alternately, the date data type. | Deterministic |
SWITCHOFFSET | SWITCHOFFSET (DATETIMEOFFSET , time_zone) | SWITCHOFFSET changes the time zone offset of a DATETIMEOFFSET value, and preserves the UTC value. | datetimeoffset with the fractional precision of the DATETIMEOFFSET | Deterministic |
TODATETIMEOFFSET | TODATETIMEOFFSET (expression , time_zone) | TODATETIMEOFFSET transforms a datetime2 value into a datetimeoffset value. TODATETIMEOFFSET interprets the datetime2 value in local time, for the specified time_zone. | datetimeoffset with the fractional precision of the datetime argument | Deterministic |
Function | Syntax | Return value | Return data type | Determinism |
---|---|---|---|---|
[@@DATEFIRST](../../t-sql/functions/datefirst-transact-sql.md) | @@DATEFIRST | Returns the current value, for the session, of SET DATEFIRST. | tinyint | Nondeterministic |
SET DATEFIRST | SET DATEFIRST { number | @*number_var* } | Sets the first day of the week to a number from 1 through 7. | Not applicable | Not applicable |
SET DATEFORMAT | SET DATEFORMAT { format | @*format_var* } | Sets the order of the dateparts (month/day/year) for entering datetime or smalldatetime data. | Not applicable | Not applicable |
[@@LANGUAGE](../../t-sql/functions/language-transact-sql.md) | @@LANGUAGE | Returns the name of the language in current used. @@LANGUAGE is not a date or time function. However, the language setting can affect the output of date functions. | Not applicable | Not applicable |
SET LANGUAGE | SET LANGUAGE { [ N ] ‘language’ | @*language_var* } | Sets the language environment for the session and system messages. SET LANGUAGE is not a date or time function. However, the language setting affects the output of date functions. | Not applicable | Not applicable |
sp_helplanguage | sp_helplanguage [ [ @language = ] ‘language’ ] | Returns information about date formats of all supported languages. sp_helplanguage is not a date or time stored procedure. However, the language setting affects the output of date functions. | Not applicable | Not applicable |
Function | Syntax | Return value | Return data type | Determinism |
---|---|---|---|---|
ISDATE | ISDATE ( expression ) | Determines whether a datetime or smalldatetime input expression has a valid date or time value. | int | 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. |
Topic | Description |
---|---|
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. |
Write International Transact-SQL Statements | Provides guidelines for portability of databases and database applications that use [!INCLUDEtsql] statements from one language to another, or that support multiple languages. |
ODBC Scalar Functions (Transact-SQL) | Provides information about ODBC scalar functions available for use in [!INCLUDEtsql] statements. This includes ODBC date and time functions. |
AT TIME ZONE (Transact-SQL) | Provides time zone conversion. |