Share via

  • Article

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

The following sections in this topic provide an overview of all Transact-SQL date and time data types and functions. For information and examples that are common to date and time data types and functions, see Using Date and Time Data.

  • Date and Time Data Types

  • Date and Time Functions

  • Function That Get System Date and Time Values

  • Functions That Get Date and Time Parts

  • Functions That Get Date and Time Difference

  • Functions That Modify Date and Time Values

  • Functions That Set or Get Session Format Functions

  • Functions That Validate Date and Time Values

  • Date and Time–Related Topics

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

Date and Time–Related Topics

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.