Was this page helpful?
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

datetime2 (Transact-SQL)

Defines a date that is combined with a time of day that is based on 24-hour clock. datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision.

For an overview of all Transact-SQL date and time data types and functions, see Date and Time Functions (Transact-SQL). For information and examples that are common to date and time data types and functions, see Using Date and Time Data.




datetime2 [ (fractional seconds precision) ]


DECLARE @MyDatetime2 datetime2(7)

CREATE TABLE Table1 ( Column1 datetime2(7) )

Default string literal format

(used for down-level client)

YYYY-MM-DD hh:mm:ss[.fractional seconds]

For more information, see the "Backward Compatibility for Down-level Clients" section of Using Date and Time Data.

Date range

0001-01-01 through 9999-12-31

January 1,1 AD through December 31, 9999 AD

Time range

00:00:00 through 23:59:59.9999999

Time zone offset range


Element ranges

YYYY is a four-digit number, ranging from 0001 through 9999, that represents a year.

MM is a two-digit number, ranging from 01 to 12, that represents a month in the specified year.

DD is a two-digit number, ranging from 01 to 31 depending on the month, that represents a day of the specified month.

hh is a two-digit number, ranging from 00 to 23, that represents the hour.

mm is a two-digit number, ranging from 00 to 59, that represents the minute.

ss is a two-digit number, ranging from 00 to 59, that represents the second.

n* is a zero- to seven-digit number from 0 to 9999999 that represents the fractional seconds.

Character length

19 positions minimum (YYYY-MM-DD hh:mm:ss ) to 27 maximum (YYYY-MM-DD hh:mm:ss.0000000)

Precision, scale

0 to 7 digits, with an accuracy of 100ns. The default precision is 7 digits.

Storage size

6 bytes for precisions less than 3; 7 bytes for precisions 3 and 4. All other precisions require 8 bytes.


100 nanoseconds

Default value

1900-01-01 00:00:00



User-defined fractional second precision


Time zone offset aware and preservation


Daylight saving aware


For data type metadata, see sys.systypes (Transact-SQL) or TYPEPROPERTY (Transact-SQL). Precision and scale are variable for some date and time data types. To obtain the precision and scale for a column, see COLUMNPROPERTY (Transact-SQL), COL_LENGTH (Transact-SQL), or sys.columns (Transact-SQL).

The following tables list the supported ISO 8601 and ODBC string literal formats for datetime2. For information about alphabetical, numeric, unseparated, and time formats for the date and time parts of datetime2, see date (Transact-SQL) and time (Transact-SQL).

ISO 8601




This format is not affected by the SET LANGUAGE and SET DATEFORMAT session locale settings. The T, the colons (:) and the period (.) are included in the string literal, for example '2007-05-02T19:58:47.1234567'.



{ ts 'yyyy-mm-dd hh:mm:ss[.fractional seconds]' }

ODBC API specific:

  • The number of digits to the right of the decimal point, which represents the fractional seconds, can be specified from 0 up to 7 (100 nanoseconds).

  • In SQL Server 2008, with compatibility level set to 10, the literal will internally map to the new time type.

The ANSI and ISO 8601 compliance of date and time apply to datetime2.

The following example compares the results of casting a string to each date and time data type.

     CAST('2007-05-08 12:35:29. 1234567 +12:15' AS time(7)) AS 'time' 
    ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS date) AS 'date' 
    ,CAST('2007-05-08 12:35:29.123' AS smalldatetime) AS 
    ,CAST('2007-05-08 12:35:29.123' AS datetime) AS 'datetime' 
    ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS datetime2(7)) AS 
    ,CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS 

Here is the result set.

Data type



12:35:29. 1234567




2007-05-08 12:35:00


2007-05-08 12:35:29.123


2007-05-08 12:35:29. 1234567


2007-05-08 12:35:29.1234567 +12:15

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

© 2015 Microsoft