| title | titleSuffix | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | helpviewer_keywords | dev_langs | monikerRange | ||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Date and Time Data Types and Functions |
SQL Server (Transact-SQL) |
Links to Date and Time data types and functions articles. |
markingmyname |
maghan |
randolphwest |
08/08/2024 |
sql |
t-sql |
reference |
|
|
=azure-sqldw-latest || =azuresqldb-current || >=sql-server-2016 || >=sql-server-linux-2017 |
[!INCLUDE sql-asdb-asdbmi-asa]
The sections in this article cover all [!INCLUDE tsql] date and time data types and functions.
- Date and time data types
- Date and time functions
- Functions 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 articles
The [!INCLUDE tsql] 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 [!INCLUDE tsql] rowversion data type isn't a date or time data type. timestamp is a deprecated synonym for rowversion.
The following tables list the [!INCLUDE tsql] date and time functions. For more information about determinism, see Deterministic and Nondeterministic Functions.
[!INCLUDE tsql] derives all system date and time values from the operating system of the computer on which the instance of [!INCLUDE ssNoVersion] runs.
Since [!INCLUDE sql2008-md], the [!INCLUDE ssde-md] 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 [!INCLUDE ssNoVersion] 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 [!INCLUDE ssNoVersion] runs. The returned value doesn't 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 [!INCLUDE ssNoVersion] 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 [!INCLUDE ssNoVersion] 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 [!INCLUDE ssNoVersion] runs. The returned value doesn't 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 [!INCLUDE ssNoVersion] runs. The returned value doesn't 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 [!INCLUDE ssNoVersion] runs. The function returns the date and time values as UTC time (Coordinated Universal Time). | datetime | Nondeterministic |
| CURRENT_DATE | CURRENT_DATE | Returns a date value containing only the date of the computer on which the instance of the [!INCLUDE ssde-md] runs. The returned value doesn't include the time and the time zone offset. | date | Nondeterministic |
| Function | Syntax | Return value | Return data type | Determinism |
|---|---|---|---|---|
| DATE_BUCKET | DATE_BUCKET ( datepart, number, date, origin ) | Returns a value corresponding to the start of each date-time bucket from the timestamp defined by the origin parameter, or the default origin value of 1900-01-01 00:00:00.000 if the origin parameter isn't specified. |
The return type depends on the argument supplied for date. | Nondeterministic |
| 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 |
| DATETRUNC | DATETRUNC ( datepart, date ) | Returns an input date truncated to a specified datepart. | The return type depends on the argument supplied for date. | 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 | @@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 | @@LANGUAGE | Returns the name of the language in current used. @@LANGUAGE isn't 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 isn't 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 isn't 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 isn't equal to 0, 100, 9, or 109. |
| Article | Description |
|---|---|
| FORMAT | Returns a value formatted with the specified format and optional culture. Use the FORMAT function for locale-aware formatting of date/time and number values as strings. |
| CAST and CONVERT | 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 [!INCLUDE tsql] statements from one language to another, or that support multiple languages. |
| ODBC Scalar Functions | Provides information about ODBC scalar functions available for use in [!INCLUDE tsql] statements. Includes ODBC date and time functions. |
| AT TIME ZONE | Provides time zone conversion. |