datetime2 (Transact-SQL)

 

Updated: June 10, 2016

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

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.

PropertyValue
Syntaxdatetime2 [ (fractional seconds precision) ]
UsageDECLARE @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 that follows.
Date range0001-01-01 through 9999-12-31

January 1,1 CE through December 31, 9999 CE
Time range00:00:00 through 23:59:59.9999999
Time zone offset rangeNone
Element rangesYYYY 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. In Informatica, the fractional seconds will be truncated when n > 3.
Character length19 positions minimum (YYYY-MM-DD hh:mm:ss ) to 27 maximum (YYYY-MM-DD hh:mm:ss.0000000)
Precision, scale0 to 7 digits, with an accuracy of 100ns. The default precision is 7 digits.
Storage size6 bytes for precisions less than 3; 7 bytes for precisions 3 and 4. All other precisions require 8 bytes.
Accuracy100 nanoseconds
Default value1900-01-01 00:00:00
CalendarGregorian
User-defined fractional second precisionYes
Time zone offset aware and preservationNo
Daylight saving awareNo

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 8601Descriptions
YYYY-MM-DDThh:mm:ss[.nnnnnnn]

YYYY-MM-DDThh:mm:ss[.nnnnnnn]
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'.
ODBCDescription
{ 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).

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

Some down-level clients do not support the time, date, datetime2 and datetimeoffset data types. The following table shows the type mapping between an up-level instance of SQL Server and down-level clients.

SQL Server data typeDefault string literal format passed to down-level clientDown-level ODBCDown-level OLEDBDown-level JDBCDown-level SQLCLIENT
timehh:mm:ss[.nnnnnnn]SQL_WVARCHAR or SQL_VARCHARDBTYPE_WSTRor DBTYPE_STRJava.sql.StringString or SqString
dateYYYY-MM-DDSQL_WVARCHAR or SQL_VARCHARDBTYPE_WSTRor DBTYPE_STRJava.sql.StringString or SqString
datetime2YYYY-MM-DD hh:mm:ss[.nnnnnnn]SQL_WVARCHAR or SQL_VARCHARDBTYPE_WSTRor DBTYPE_STRJava.sql.StringString or SqString
datetimeoffsetYYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mmSQL_WVARCHAR or SQL_VARCHARDBTYPE_WSTRor DBTYPE_STRJava.sql.StringString or SqString

When you convert to date and time data types, SQL Server rejects all values it cannot recognize as dates or times. For information about using the CAST and CONVERT functions with date and time data, see CAST and CONVERT (Transact-SQL)

Converting Other Date and Time Types to the datetime2 Data Type

This section describes what occurs when other date and time data types are converted to the datetime2 data type.

When the conversion is from date, the year, month and day are copied. The time component is set to 00:00:00.0000000. The following code shows the results of converting a date value to a datetime2 value.

DECLARE @date date = '12-21-16';
DECLARE @datetime2 datetime2 = @date;

SELECT @datetime2 AS '@datetime2', @date AS '@date';
  
--Result  
--@datetime2                  @date
----------------------------- ----------
--2016-12-21 00:00:00.0000000 2016-12-21

When the conversion is from time(n), the time component is copied, and the date component is set to '1900-01-01'. The following example shows the results of converting a time(7) value to a datetime2 value.

DECLARE @time time(7) = '12:10:16.1234567';
DECLARE @datetime2 datetime2 = @time;

SELECT @datetime2 AS '@datetime2', @time AS '@time';
  
--Result  
--@datetime2                  @time
----------------------------- ----------------
--1900-01-01 12:10:16.1234567 12:10:16.1234567

When the conversion is from smalldatetime, the hours and minutes are copied. The seconds and fractional seconds are set to 0. The following code shows the results of converting a smalldatetime value to a datetime2 value.

DECLARE @smalldatetime smalldatetime = '12-01-16 12:32';
DECLARE @datetime2 datetime2 = @smalldatetime;

SELECT @datetime2 AS '@datetime2', @smalldatetime AS '@smalldatetime'; 
  
--Result  
--@datetime2                  @smalldatetime
----------------------------- -----------------------
--2016-12-01 12:32:00.0000000 2016-12-01 12:32:00 

When the conversion is from datetimeoffset(n), the date and time components are copied. The time zone is truncated. The following example shows the results of converting a datetimeoffset(7) value to a datetime2 value.

DECLARE @datetimeoffset datetimeoffset(7) = '2016-10-23 12:45:37.1234567 +10:0';
DECLARE @datetime2 datetime2 = @datetimeoffset;

SELECT @datetime2 AS '@datetime2', @datetimeoffset AS '@datetimeoffset'; 
  
--Result  
--@datetime2                  @datetimeoffset
----------------------------- ----------------------------------
--2016-10-23 12:45:37.1234567 2016-10-23 12:45:37.1234567 +10:00

When the conversion is from datetime, the date and time are copied. The fractional precision is extended to 7 digits. The following example shows the results of converting a datetime value to a datetime2 value.

DECLARE @datetime datetime = '2016-10-23 12:45:37.333';
DECLARE @datetime2 datetime2 = @datetime;

SELECT @datetime2 AS '@datetime2', @datetime AS '@datetime';
   
--Result  
--@datetime2                  @datetime
------------------------- ---------------------------
--2016-10-23 12:45:37.3333333 2016-10-23 12:45:37.333

Converting String Literals to datetime2

Conversions from string literals to date and time types are permitted if all parts of the strings are in valid formats. Otherwise, a runtime error is raised. Implicit conversions or explicit conversions that do not specify a style, from date and time types to string literals will be in the default format of the current session. The following table shows the rules for converting a string literal to the datetime2 data type.

Input string literaldatetime2(n)
ODBC DATEODBC string literals are mapped to the datetime data type. Any assignment operation from ODBC DATETIME literals into datetime2 types will cause an implicit conversion between datetime and this type as defined by the conversion rules.
ODBC TIMESee previous ODBC DATE rule.
ODBC DATETIMESee previous ODBC DATE rule.
DATE onlyThe TIME part defaults to 00:00:00.
TIME onlyThe DATE part defaults to 1900-1-1.
TIMEZONE onlyDefault values are supplied.
DATE + TIMETrivial
DATE + TIMEZONENot allowed.
TIME + TIMEZONEThe DATE part defaults to 1900-1-1. TIMEZONE input is ignored.
DATE + TIME + TIMEZONEThe local DATETIME will be used.

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

SELECT   
     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   
        'smalldatetime'   
    ,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   
        'datetime2'  
    ,CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS   
        'datetimeoffset';  

Here is the result set.

Data typeOutput
time12:35:29. 1234567
date2007-05-08
smalldatetime2007-05-08 12:35:00
datetime2007-05-08 12:35:29.123
datetime22007-05-08 12:35:29. 1234567
datetimeoffset2007-05-08 12:35:29.1234567 +12:15

CAST and CONVERT (Transact-SQL)

Community Additions

ADD
Show: