Date and Time Functions (Transact-SQL)

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

The Transact-SQL rowversion 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 2008 R2 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 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.	datetime	Nondeterministic
GETDATE	GETDATE ( )	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.	datetime	Nondeterministic
GETUTCDATE	GETUTCDATE ( )	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).	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 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

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

Topic	Description
Using Date and Time Data	Provides information and examples that are common to date and time data types and functions.
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.
Writing 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.
Data Type Mapping with Distributed Queries	Provides information about how date and time data types affect distributed queries between servers that have different versions of SQL Server or different providers.