SQL Server Date and Time Data Types and Functions Explained

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics

This article provides a comprehensive guide to all Transact-SQL date and time data types and functions available in SQL Server, Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics. Understanding these data types and functions is crucial for effectively managing and manipulating date and time data within your databases.

Understanding SQL Server Date and Time Data Types

SQL Server offers a variety of data types specifically designed to store date and time information. Each type varies in terms of range, accuracy, storage size, and features. Choosing the appropriate data type is essential for optimizing storage and ensuring data integrity. Here’s a detailed overview of the available 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

It’s important to note that the rowversion data type (deprecated synonym timestamp) is not a date or time data type, but rather a data type used for row versioning.

Exploring SQL Server Date and Time Functions

SQL Server provides a rich set of built-in functions to work with date and time data. These functions can be broadly categorized based on their purpose, such as retrieving system date and time, extracting date parts, constructing date and time values, calculating differences, modifying dates, and managing session formats.

Functions for Retrieving System Date and Time Values

These functions retrieve the current date and time from the operating system of the computer running the SQL Server instance. It’s important to understand the precision differences between these functions, especially when accuracy is critical.

Higher-Precision System Date and Time Functions

Introduced in SQL Server 2008, these functions utilize the GetSystemTimeAsFileTime() Windows API, offering a higher precision of 100 nanoseconds. The actual accuracy depends on the underlying hardware and Windows version. You can use GetSystemTimeAdjustment() to determine the specific accuracy of your system.

Function	Syntax	Return value	Return data type	Determinism
SYSDATETIME	SYSDATETIME ( )	Returns the current date and time of the SQL Server computer. Time zone offset is not included.	datetime2(7)	Nondeterministic
SYSDATETIMEOFFSET	SYSDATETIMEOFFSET ( )	Returns the current date and time with the time zone offset of the SQL Server computer.	datetimeoffset(7)	Nondeterministic
SYSUTCDATETIME	SYSUTCDATETIME ( )	Returns the current UTC (Coordinated Universal Time) date and time of the SQL Server computer.	datetime2(7)	Nondeterministic

Lower-Precision System Date and Time Functions

These are the older date and time functions, providing date and time values with millisecond precision.

Function	Syntax	Return value	Return data type	Determinism
CURRENT_TIMESTAMP	CURRENT_TIMESTAMP	Returns the current date and time of the SQL Server computer. Time zone offset is not included.	datetime	Nondeterministic
GETDATE	GETDATE ( )	Returns the current date and time of the SQL Server computer. Time zone offset is not included.	datetime	Nondeterministic
GETUTCDATE	GETUTCDATE ( )	Returns the current UTC date and time of the SQL Server computer.	datetime	Nondeterministic
CURRENT_DATE	CURRENT_DATE	Returns the current date only of the SQL Server computer, without time or time zone offset.	date	Nondeterministic

Functions for Extracting Date and Time Parts

These functions allow you to extract specific components from a date or time value, such as the year, month, day, hour, minute, etc.

Function	Syntax	Return value	Return data type	Determinism
DATE_BUCKET	DATE_BUCKET ( datepart, number, date, origin )	Returns the start of the date-time bucket based on the specified datepart, number, and origin.	Data type depends on the date argument.	Nondeterministic
DATENAME	DATENAME ( datepart, date )	Returns the name of the specified datepart (e.g., ‘January’, ‘Monday’).	nvarchar	Nondeterministic
DATEPART	DATEPART ( datepart, date )	Returns the integer value of the specified datepart (e.g., 1 for January, 2 for Monday).	int	Nondeterministic
DATETRUNC	DATETRUNC ( datepart, date )	Returns the date value truncated to the beginning of the specified datepart.	Data type depends on the date argument.	Nondeterministic
DAY	DAY ( date )	Returns the day of the month from the date value.	int	Deterministic
MONTH	MONTH ( date )	Returns the month from the date value.	int	Deterministic
YEAR	YEAR ( date )	Returns the year from the date value.	int	Deterministic

Functions for Constructing Date and Time Values from Parts

These functions allow you to create date and time values by combining individual parts like year, month, day, hour, minute, etc.

Function	Syntax	Return value	Return data type	Determinism
DATEFROMPARTS	DATEFROMPARTS ( year, month, day )	Creates a date value from the provided year, month, and day.	date	Deterministic
DATETIME2FROMPARTS	DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )	Creates a datetime2 value from the provided date and time parts with specified precision.	datetime2(**precision)**	Deterministic
DATETIMEFROMPARTS	DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )	Creates a datetime value from the provided date and time parts.	datetime	Deterministic
DATETIMEOFFSETFROMPARTS	DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )	Creates a datetimeoffset value with specified date, time, time zone offset, and precision.	datetimeoffset(**precision)**	Deterministic
SMALLDATETIMEFROMPARTS	SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )	Creates a smalldatetime value from the provided date and time parts.	smalldatetime	Deterministic
TIMEFROMPARTS	TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )	Creates a time value from the provided time parts with specified precision.	time(**precision)**	Deterministic

Functions for Calculating Date and Time Differences

These functions calculate the difference between two date or time values based on a specified date part.

Function	Syntax	Return value	Return data type	Determinism
DATEDIFF	DATEDIFF ( datepart, startdate, enddate )	Returns the count of datepart boundaries crossed between startdate and enddate.	int	Deterministic
DATEDIFF_BIG	DATEDIFF_BIG ( datepart, startdate, enddate )	Returns the count of datepart boundaries crossed between startdate and enddate. Returns bigint for larger differences.	bigint	Deterministic

Functions for Modifying Date and Time Values

These functions allow you to manipulate date and time values, such as adding intervals, getting the end of the month, or converting time zones.

Function	Syntax	Return value	Return data type	Determinism
DATEADD	DATEADD (datepart, number, date )	Adds a number of datepart intervals to the date.	Same data type as the date argument.	Deterministic
EOMONTH	EOMONTH ( start_date [ , month_to_add ] )	Returns the last day of the month for the start_date, optionally adding months.	Same data type as start_date or date.	Deterministic
SWITCHOFFSET	SWITCHOFFSET (DATETIMEOFFSET, time_zone )	Changes the time zone offset of a DATETIMEOFFSET value while preserving the UTC value.	datetimeoffset	Deterministic
TODATETIMEOFFSET	TODATETIMEOFFSET (expression, time_zone )	Converts a datetime2 value to a datetimeoffset value, interpreting the datetime2 value in the specified time_zone.	datetimeoffset	Deterministic

Functions for Setting or Returning Session Format Settings

These functions manage session-specific date and time formatting settings, influencing how date values are interpreted and displayed.

Function	Syntax	Return value	Return data type	Determinism
@@DATEFIRST	@@DATEFIRST	Returns the current session’s SET DATEFIRST value (first day of the week).	tinyint	Nondeterministic
SET DATEFIRST	SET DATEFIRST { number \| @number_var }	Sets the first day of the week for the current session.	Not applicable	Not applicable
SET DATEFORMAT	SET DATEFORMAT { format \| @format_var }	Sets the datepart order (month/day/year) for interpreting datetime and smalldatetime input strings in the current session.	Not applicable	Not applicable
@@LANGUAGE	@@LANGUAGE	Returns the name of the current session’s language.	Not applicable	Not applicable
SET LANGUAGE	SET LANGUAGE { [ N ] ‘language‘ \| @language_var }	Sets the language for the current session, affecting date function output.	Not applicable	Not applicable
sp_helplanguage	sp_helplanguage [ [ @language = ] ‘language‘ ]	Returns information about date formats for all supported languages.	Not applicable	Not applicable

Functions for Validating Date and Time Values

Function	Syntax	Return value	Return data type	Determinism
ISDATE	ISDATE ( expression )	Checks if an expression is a valid datetime or smalldatetime value. Returns 1 if valid, 0 if invalid.	int	Deterministic under specific CONVERT function usage.

Further Resources for Date and Time in SQL Server

To deepen your understanding and usage of date and time functionalities in SQL Server, explore these related articles:

Article	Description
FORMAT	Learn how to format date/time and number values as strings with locale-awareness using the FORMAT function.
CAST and CONVERT	Understand how to convert date and time values to and from string literals and other date/time formats.
Write International Transact-SQL Statements	Get guidelines for creating portable databases and applications using Transact-SQL across different languages and supporting multiple languages.
ODBC Scalar Functions	Discover ODBC scalar functions, including date and time functions, available for use in Transact-SQL statements.
AT TIME ZONE	Explore time zone conversion capabilities in SQL Server using the AT TIME ZONE clause.