Date and Time Data Types and Functions (Transact-SQL)

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2012)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

The following sections in this topic provide an overview of all Transact-SQL date and time data types and functions.

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-SQLrowversion data type is not a date or time data type. timestamp is a deprecated synonym for rowversion.

Date and Time Functions

The Transact-SQL date and time functions are listed in the following tables. For more information about determinism, see Deterministic and Nondeterministic Functions.

Functions That Get System Date and Time Values

All system date and time values are derived from the operating system of the computer on which the instance of SQL Server is running.

Higher-Precision System Date and Time Functions

SQL Server 2017 obtains the date and time values by using the GetSystemTimeAsFileTime() Windows API. The accuracy depends on the computer hardware and version of Windows on which the instance of SQL Server is running. The precision of this API is fixed at 100 nanoseconds. The accuracy can be determined by using the GetSystemTimeAdjustment() Windows API.

Function Syntax Return value Return data type Determinism
SYSDATETIME SYSDATETIME () Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is not included. datetime2(7) Nondeterministic
SYSDATETIMEOFFSET SYSDATETIMEOFFSET ( ) Returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is included. datetimeoffset(7) Nondeterministic
SYSUTCDATETIME SYSUTCDATETIME ( ) Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The date and time is returned 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 that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is not included. datetime Nondeterministic
GETDATE GETDATE ( ) Returns a datetime value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is not included. datetime Nondeterministic
GETUTCDATE GETUTCDATE ( ) Returns a datetime value that contains the date and time of the computer on which the instance of SQL Server is running. The date and time is returned as UTC time (Coordinated Universal Time). datetime Nondeterministic

Functions That Get Date and Time Parts

Function Syntax Return value Return data type Determinism
DATENAME DATENAME ( datepart , date ) Returns a character string that represents the specified datepart of the specified date. nvarchar Nondeterministic
DATEPART DATEPART ( datepart , date ) Returns an integer that represents the specified datepart of the specified date. int Nondeterministic
DAY DAY ( date ) Returns an integer that represents the day day part of the specified date. int Deterministic
MONTH MONTH ( date ) Returns an integer that represents the month part of a specified date. int Deterministic
YEAR YEAR ( date ) Returns an integer that represents the year part of a specified date. int Deterministic

Functions That Get 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 and 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 and with the specified offsets and precision. datetime( 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 and with the specified precision. time( precision ) Deterministic

Functions That Get Date and Time Difference

Function Syntax Return value Return data type Determinism
DATEDIFF DATEDIFF ( datepart , startdate , enddate ) Returns the number of date or time datepart boundaries that are crossed between two specified dates. int Deterministic
DATEDIFF_BIG DATEDIFF_BIG ( datepart , startdate , enddate ) Returns the number of date or time datepart boundaries that are 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 that contains the specified date, with an optional offset. Return type is the type of start_date or date. 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. The datetime2 value is interpreted in local time for the specified time_zone. datetimeoffset with the fractional precision of the datetime argument Deterministic

Functions That Set or Get Session Format

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 that is currently being 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

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 is a valid date or time value. int ISDATE is deterministic only if you use it 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 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 Transact-SQL statements from one language to another, or that support multiple languages.
ODBC Scalar Functions (Transact-SQL) Provides information about ODBC scalar functions that can be used in Transact-SQL statements. This includes ODBC date and time functions.
AT TIME ZONE (Transact-SQL) Provides time zone conversion.

See Also

Functions
Data Types (Transact-SQL)