Export (0) Print
Expand All

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

The 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 Note

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

The 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 is running.

Higher-Precision System Date and Time Functions

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 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 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 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 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 datetime value that contains the date and time of the computer on which the instance of is running. The time zone offset is not included.

datetime

Nondeterministic

GETDATE

GETDATE ( )

Returns a datetime value that contains the date and time of the computer on which the instance of is running. The time zone offset is not included.

datetime

Nondeterministic

GETUTCDATE

GETUTCDATE ( )

Returns a datetime value that contains the date and time of the computer on which the instance of 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 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 and 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 and with the specified offsets and precision.

datetime ( 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 and with the specified precision.

time ( precision )

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

EOMONTH

EOMONTH ( 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

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

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  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  statements. This includes ODBC date and time functions.

Community Additions

ADD
Show:
© 2014 Microsoft