TechNet
Export (0) Print
Expand All

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

 

Updated: December 2, 2015

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

The Transact-SQL date and time data types are listed in the following table.

Data typeFormatRangeAccuracyStorage size (bytes)User-defined fractional second precisionTime zone offset
timehh:mm:ss[.nnnnnnn]00:00:00.0000000 through 23:59:59.9999999100 nanoseconds3 to 5YesNo
dateYYYY-MM-DD0001-01-01 through 9999-12-311 day3NoNo
smalldatetimeYYYY-MM-DD hh:mm:ss1900-01-01 through 2079-06-061 minute4NoNo
datetimeYYYY-MM-DD hh:mm:ss[.nnn]1753-01-01 through 9999-12-310.00333 second8NoNo
datetime2YYYY-MM-DD hh:mm:ss[.nnnnnnn]0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999100 nanoseconds6 to 8YesNo
datetimeoffsetYYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC)100 nanoseconds8 to 10YesYes
System_CAPS_ICON_note.jpg Note


The Transact-SQLrowversion data type is not a date or time data type. timestamp is a deprecated synonym for rowversion.

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

FunctionSyntaxReturn valueReturn data typeDeterminism
SYSDATETIMESYSDATETIME ()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
SYSDATETIMEOFFSETSYSDATETIMEOFFSET ( )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
SYSUTCDATETIMESYSUTCDATETIME ( )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

FunctionSyntaxReturn valueReturn data typeDeterminism
CURRENT_TIMESTAMPCURRENT_TIMESTAMPReturns 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.datetimeNondeterministic
GETDATEGETDATE ( )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.datetimeNondeterministic
GETUTCDATEGETUTCDATE ( )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).datetimeNondeterministic

Functions That Get Date and Time Parts

FunctionSyntaxReturn valueReturn data typeDeterminism
DATENAMEDATENAME ( datepart , date )Returns a character string that represents the specified datepart of the specified date.nvarcharNondeterministic
DATEPARTDATEPART ( datepart , date )Returns an integer that represents the specified datepart of the specified date.intNondeterministic
DAYDAY ( date )Returns an integer that represents the day day part of the specified date.intDeterministic
MONTHMONTH ( date )Returns an integer that represents the month part of a specified date.intDeterministic
YEARYEAR ( date )Returns an integer that represents the year part of a specified date.intDeterministic

Functions That Get Date and Time Values from Their Parts

FunctionSyntaxReturn valueReturn data typeDeterminism
DATEFROMPARTSDATEFROMPARTS ( year, month, day )Returns a date value for the specified year, month, and day.dateDeterministic
DATETIME2FROMPARTSDATETIME2FROMPARTS ( 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
DATETIMEFROMPARTSDATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )Returns a datetime value for the specified date and time.datetimeDeterministic
DATETIMEOFFSETFROMPARTSDATETIMEOFFSETFROMPARTS ( 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
SMALLDATETIMEFROMPARTSSMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )Returns a smalldatetime value for the specified date and time.smalldatetimeDeterministic
TIMEFROMPARTSTIMEFROMPARTS ( 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

FunctionSyntaxReturn valueReturn data typeDeterminism
DATEDIFFDATEDIFF ( datepart , startdate , enddate )Returns the number of date or time datepart boundaries that are crossed between two specified dates.intDeterministic
DATEDIFF_BIGDATEDIFF_BIG ( datepart , startdate , enddate )Returns the number of date or time datepart boundaries that are crossed between two specified dates.bigintDeterministic

Functions That Modify Date and Time Values

FunctionSyntaxReturn valueReturn data typeDeterminism
DATEADDDATEADD (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 argumentDeterministic
EOMONTHEOMONTH ( 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
SWITCHOFFSETSWITCHOFFSET (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 DATETIMEOFFSETDeterministic
TODATETIMEOFFSETTODATETIMEOFFSET (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 argumentDeterministic

Functions That Set or Get Session Format

FunctionSyntaxReturn valueReturn data typeDeterminism
@@DATEFIRST@@DATEFIRSTReturns the current value, for the session, of SET DATEFIRST.tinyintNondeterministic
SET DATEFIRSTSET DATEFIRST { number | @number_var }Sets the first day of the week to a number from 1 through 7.Not applicableNot applicable
SET DATEFORMATSET DATEFORMAT { format | @format_var }Sets the order of the dateparts (month/day/year) for entering datetime or smalldatetime data.Not applicableNot applicable
@@LANGUAGE@@LANGUAGEReturns 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 applicableNot applicable
SET LANGUAGESET 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 applicableNot applicable
sp_helplanguagesp_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 applicableNot applicable

Functions That Validate Date and Time Values

FunctionSyntaxReturn valueReturn data typeDeterminism
ISDATEISDATE ( expression )Determines whether a datetime or smalldatetime input expression is a valid date or time value.intISDATE 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.
TopicDescription
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 StatementsProvides 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.

Built-in Functions (Transact-SQL)
Data Types (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft