Date and time data types and functions (Transact-SQL)

  • Article

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

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.
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