Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
SQL database in Microsoft Fabric
The sections in this article cover all Transact-SQL date and time data types and functions, including usage and examples.
Date and time data types
The following table lists the Transact-SQL date and time data types.
| 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 runs. 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() |
The SYSDATETIME function 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 () |
The SYSDATETIMEOFFSET function 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 () |
The SYSUTCDATETIME function returns a datetime2(7) value containing the date and time of the computer on which the instance of SQL Server is running. SYSUTCDATETIME 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 |
The CURRENT_TIMESTAMP function 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() |
The GETDATE function 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() |
The GETUTCDATE function returns a datetime value containing the date and time of the computer on which the instance of SQL Server runs. The GETUTCDATE function returns the date and time values as UTC time (Coordinated Universal Time). |
datetime | Nondeterministic |
| CURRENT_DATE | CURRENT_DATE |
The CURRENT_DATE function 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>) |
The DATE_BUCKET function 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> ) |
The DATENAME function returns a character string representing the specified datepart of the specified date. |
nvarchar | Nondeterministic |
| DATEPART | DATEPART ( <datepart>, <date> ) |
The DATEPART function returns an integer representing the specified datepart of the specified date. |
int | Nondeterministic |
| DATETRUNC | DATETRUNC ( <datepart>, <date> ) |
The DATETRUNC function returns an input date truncated to a specified datepart. |
The return type depends on the argument supplied for date. | Nondeterministic |
| DAY | DAY ( <date> ) |
The DAY function returns an integer representing the day part of the specified date. |
int | Deterministic |
| MONTH | MONTH ( <date> ) |
The MONTH function returns an integer representing the month part of a specified date. |
int | Deterministic |
| YEAR | YEAR ( <date> ) |
The YEAR function 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> ) |
The DATEFROMPARTS function returns a date value for the specified year, month, and day. |
date | Deterministic |
| DATETIME2FROMPARTS | DATETIME2FROMPARTS ( <year>, <month>, <day>, <hour>, <minute>, <seconds>, <fractions>, <precision> ) |
The DATETIME2FROMPARTS function 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> ) |
The DATETIMEFROMPARTS function 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> ) |
The DATETIMEOFFSETFROMPARTS function 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> ) |
The SMALLDATETIMEFROMPARTS function returns a smalldatetime value for the specified date and time. |
smalldatetime | Deterministic |
| TIMEFROMPARTS | TIMEFROMPARTS ( <hour>, <minute>, <seconds>, <fractions>, <precision> ) |
The TIMEFROMPARTS function 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> ) |
The DATEDIFF function returns the number of date or time datepart boundaries, crossed between two specified dates. |
int | Deterministic |
| DATEDIFF_BIG | DATEDIFF_BIG ( <datepart>, <startdate>, <enddate> ) |
The DATEDIFF_BIG function 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> ) |
The DATEADD function 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> ] ) |
The EOMONTH function 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> ) |
The SWITCHOFFSET function returns changes the time zone offset of a datetimeoffset value, and preserves the UTC value. |
datetimeoffset with the fractional precision of the DATETIMEOFFSET argument | Deterministic |
| TODATETIMEOFFSET | TODATETIMEOFFSET (<expression>, <time_zone> ) |
The TODATETIMEOFFSET function 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 |
Tip
For more information and recommendations about manipulating time zone information in SQL Server with the datetimeoffset data type, see AT TIME ZONE.
Functions that set or return session format functions
| Function | Syntax | Return value | Return data type | Determinism |
|---|---|---|---|---|
| @@DATEFIRST | @@DATEFIRST |
The @@DATEFIRST function returns the current value, for the session, of SET DATEFIRST. |
tinyint | Nondeterministic |
| SET DATEFIRST | SET DATEFIRST { *number* } or SET DATEFIRST { *@number_var* } |
The SET DATEFIRST statement sets the first day of the week to a number from 1 through 7. |
Not applicable | Not applicable |
| SET DATEFORMAT | SET DATEFORMAT { *format* } or SET DATEFORMAT { *@format_var* } |
The SET DATEFORMAT statement sets the order of the date parts (month/day/year) for entering datetime or smalldatetime data. |
Not applicable | Not applicable |
| @@LANGUAGE | @@LANGUAGE |
The @@LANGUAGE function 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*' } or SET LANGUAGE { *@language_var* } |
Sets the language environment for the session and system messages. SET LANGUAGE isn't a date or time function, but the language setting affects the output of date functions. |
Not applicable | Not applicable |
| sp_helplanguage | sp_helplanguage [ [ *@language* = ] '*language*' ] |
The sp_helplanguage function returns information about date formats of all supported languages. 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> ) |
The ISDATE function determines whether a datetime or smalldatetime input expression has a valid date or time value. |
int | The ISDATE function 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 | The FORMAT function 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 | The CAST and CONVERT functions convert 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. |