Date and time data types and functions (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
The sections in this article cover all Transact-SQL 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
Date and time data types
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 isn't a date or time data type. timestamp is a deprecated synonym for rowversion.
Date and time functions
The following tables list the Transact-SQL date and time functions. For more information about determinism, see Deterministic and Nondeterministic Functions.
Functions that return system date and time values
Transact-SQL derives all system date and time values from the operating system of the computer on which the instance of SQL Server runs.
Higher-precision system date and time functions
Since SQL Server 2008 (10.0.x), the Database Engine 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 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 SQL Server 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 SQL Server 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 SQL Server is running. The function returns the date and time values as UTC time (Coordinated Universal Time). | datetime2(7) | Nondeterministic |
Lower-precision system date and time functions
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 SQL Server 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 SQL Server 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 SQL Server 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 Database Engine runs. The returned value doesn't include the time and the time zone offset. | date | Nondeterministic |
Functions that return date and time parts
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 |
Functions that return date and time values from their parts
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 |
Functions that return date and time difference values
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 |
Functions that modify date and time values
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 |
Functions that set or return session format functions
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 |
Functions that validate date and time values
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. |
Date and time-related articles
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 Transact-SQL statements from one language to another, or that support multiple languages. |
ODBC Scalar Functions | Provides information about ODBC scalar functions available for use in Transact-SQL statements. Includes ODBC date and time functions. |
AT TIME ZONE | Provides time zone conversion. |
Related content
Tilbakemeldinger
https://aka.ms/ContentUserFeedback.
Kommer snart: Gjennom 2024 faser vi ut GitHub Issues som tilbakemeldingsmekanisme for innhold, og erstatter det med et nytt system for tilbakemeldinger. Hvis du vil ha mer informasjon, kan du se:Send inn og vis tilbakemelding for