datetime2 (Transact-SQL)

Artikel
08/28/2013

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.

datetime2 Description

Property	Value
Syntax	datetime2 [ (fractional seconds precision) ]
Usage	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 that follows.
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	None
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.
Accuracy	100 nanoseconds
Default value	1900-01-01 00:00:00
Calendar	Gregorian
User-defined fractional second precision	Yes
Time zone offset aware and preservation	No
Daylight saving aware	No

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

Supported String Literal Formats for datetime2

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	Descriptions
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'.

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

ODBC	Description
{ 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 2012, with compatibility level set to 10, the literal will internally map to the new time type.

{ 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 2012, with compatibility level set to 10, the literal will internally map to the new time type.

ANSI and ISO 8601 Compliance

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

Backward Compatibility for Down-level Clients

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 2012 data type	Default string literal format passed to down-level client	Down-level ODBC	Down-level OLEDB	Down-level JDBC	Down-level SQLCLIENT
time	hh:mm:ss[.nnnnnnn]	SQL_WVARCHAR or SQL_VARCHAR	DBTYPE_WSTRor DBTYPE_STR	Java.sql.String	String or SqString
date	YYYY-MM-DD	SQL_WVARCHAR or SQL_VARCHAR	DBTYPE_WSTRor DBTYPE_STR	Java.sql.String	String or SqString
datetime2	YYYY-MM-DD hh:mm:ss[.nnnnnnn]	SQL_WVARCHAR or SQL_VARCHAR	DBTYPE_WSTRor DBTYPE_STR	Java.sql.String	String or SqString
datetimeoffset	YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+\|-]hh:mm	SQL_WVARCHAR or SQL_VARCHAR	DBTYPE_WSTRor DBTYPE_STR	Java.sql.String	String or SqString

Converting Date and Time Data

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 datetime2 Data Type to Other Date and Time Types

The following table describes what occurs when a datetime2 data type is converted to other date and time data types.

Data type to convert to	Conversion details
date	The year, month, and day are copied. The following code shows the results of converting a datetime2(4) value to a date value. `DECLARE @datetime2 datetime2(4) = '12-10-25 12:32:10.1234'; DECLARE @date date = @datetime2; SELECT @datetime2 AS '@datetime2', @date AS 'date'; --Result --@datetime2 date -------------------------- ---------- --2025-12-10 12:32:10.1234 2025-12-10 -- --(1 row(s) affected)`
time(n)	The hour, minute, second, and fractional seconds are copied if the scale is the same and rounded up if the scale is different. The following code shows the results of converting a datetime2(4) value to a time(3) value. `DECLARE @datetime2 datetime2(4) = '12-10-25 12:32:10.1237'; DECLARE @time time(3) = @datetime2; SELECT @datetime2 AS '@datetime2', @time AS 'time(3)'; --Result --@datetime2 time(3) -------------------------- ------------ --2025-12-10 12:32:10.1234 12:32:10.124 -- --(1 row(s) affected)`
datetime	The date and time values are copied. When the fractional precision of the datetime(n) value is greater than three digits, the value is truncated. The following code shows the results of converting a datetime2 value to a datetime value. `DECLARE @datetime2 datetime2 = '12-10-25 12:32:10.1234567' DECLARE @datetime datetime = @datetime2; SELECT @datetime2 AS '@datetime2', @datetime AS '@datetime'; --Result --@datetime2 @datetime ----------------------------- ----------------------- @datetime2 @datetime ---------------------- ----------------------- 2025-12-10 12:32:10.12 2025-12-10 12:32:10.123 -- --(1 row(s) affected)`
smalldatetime	The date and hours are copied. The minutes are rounded up with respect to the seconds and the seconds are set to 0. The following code shows the results of converting a datetime2 value to a smalldatetime value. `DECLARE @datetime2 datetime2 = '12-10-25 12:32:30.9234567'; DECLARE @smalldatetime smalldatetime = @datetime2; SELECT @datetime2 AS '@datetime2', @smalldatetime AS '@smalldatetime'; --Result @datetime2 @smalldatetime ---------------------- ----------------------- 2025-12-10 12:32:30.92 2025-12-10 12:33:00 (1 row(s) affected)`
datetimeoffset(n)	The datetime2(n) value is copied to the datetimeoffset(n) value. The time zone offset is set to +00:0. When the precision of the datetime2(n) value is greater than the precision of datetimeoffset(n) value, the value is rounded up to fit. The following code shows the results of converting a datetime2(5) value to a datetimeoffset(3) value. `D DECLARE @datetime2 datetime2(3) = '12-10-25 12:32:10.12999'; DECLARE @datetimeoffset datetimeoffset(2) = @datetime2; SELECT @datetime2 AS '@datetime2', @datetimeoffset AS '@datetimeoffset(2)'; --Result --@datetime2 @datetimeoffset(2) ------------------------- ----------------------------- 2025-12-10 12:32:10.13 2025-12-10 12:32:10.13 +00:00 -- --(1 row(s) affected)`

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 literal	datetime2(n)
ODBC DATE	ODBC 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 TIME	See previous ODBC DATE rule.
ODBC DATETIME	See previous ODBC DATE rule.
DATE only	The TIME part defaults to 00:00:00.
TIME only	The DATE part defaults to 1900-1-1.
TIMEZONE only	Default values are supplied.
DATE + TIME	Trivial
DATE + TIMEZONE	Not allowed.
TIME + TIMEZONE	The DATE part defaults to 1900-1-1. TIMEZONE input is ignored.
DATE + TIME + TIMEZONE	The local DATETIME will be used.

Examples

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 type	Output
time	12:35:29. 1234567
date	2007-05-08
smalldatetime	2007-05-08 12:35:00
datetime	2007-05-08 12:35:29.123
datetime2	2007-05-08 12:35:29. 1234567
datetimeoffset	2007-05-08 12:35:29.1234567 +12:15

Dela via