datetime2 (Transact-SQL)

מאמר
10/25/2024

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

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[.nnnnnnn]` For more information, see Backward compatibility for down-level clients later in this article.
Date range	`0001-01-01` through `9999-12-31` January 1, 1 CE through December 31, 9999 CE
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`, which represents a year. `MM` is a two-digit number, ranging from `01` to `12`, which represents a month in the specified year. `dd` is a two-digit number, ranging from `01` to `31` depending on the month, which represents a day of the specified month. `HH` is a two-digit number, ranging from `00` to `23`, which represents the hour. `mm` is a two-digit number, ranging from `00` to `59`, which represents the minute. `ss` is a two-digit number, ranging from `00` to `59`, which represents the second. `n*` is a zero- to seven-digit number from `0` to `9999999`, which represents the fractional seconds. In Informatica, the fractional seconds are truncated when n is less than `3`.
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 100 nanoseconds (100 ns). The default precision is 7 digits. In Microsoft Fabric, this precision can be an integer from 0 to 6, with no default. Precision must be specified in Microsoft Fabric.
Storage size ¹	6 bytes for precision less than 3. 7 bytes for precision 3 or 4. All other precision requires 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

¹ Provided values are for uncompressed rowstore. Use of data compression or columnstore might alter storage size for each precision. Additionally, storage size on disk and in memory might differ. For example, datetime2 values always require 8 bytes in memory when batch mode is used.

² When a datetime2 value is cast to a varbinary value, an extra byte is added to the varbinary value to store precision.

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

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 and time.

ISO 8601	Descriptions
`yyyy-MM-ddTHH:mm:ss[.nnnnnnn]`	This format isn't 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 `2024-05-02T19:58:47.1234567`.

ODBC	Description
`{ ts 'yyyy-MM-dd HH:mm:ss[.nnnnnnn]' }`	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).

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 don't 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 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

Convert date and time data

When you convert to date and time data types, the Database Engine rejects all values it can't recognize as dates or times. For information about using the CAST and CONVERT functions with date and time data, see CAST and CONVERT

Convert 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 AS DATE = '12-21-16';

DECLARE @datetime2 AS DATETIME2 = @date;

SELECT @datetime2 AS '@datetime2',
       @date AS '@date';

Here's the result set.

@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 AS TIME (7) = '12:10:16.1234567';

DECLARE @datetime2 AS DATETIME2 = @time;

SELECT @datetime2 AS '@datetime2',
       @time AS '@time';

Here's the result set.

@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 AS SMALLDATETIME = '12-01-16 12:32';

DECLARE @datetime2 AS DATETIME2 = @smalldatetime;

SELECT @datetime2 AS '@datetime2',
       @smalldatetime AS '@smalldatetime';

Here's the result set.

@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 AS DATETIMEOFFSET (7) = '2016-10-23 12:45:37.1234567 +10:0';

DECLARE @datetime2 AS DATETIME2 = @datetimeoffset;

SELECT @datetime2 AS '@datetime2',
       @datetimeoffset AS '@datetimeoffset';

Here's the result set.

@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 AS DATETIME = '2016-10-23 12:45:37.333';

DECLARE @datetime2 AS DATETIME2 = @datetime;

SELECT @datetime2 AS '@datetime2',
       @datetime AS '@datetime';

Here's the result set.

@datetime2                  @datetime
----------------------- ---------------------------
2016-10-23 12:45:37.3333333 2016-10-23 12:45:37.333

Cast explicitly to datetime2 when using datetime

Under database compatibility level 130 and greater, implicit conversions from datetime to datetime2 data types show improved accuracy by accounting for the fractional milliseconds, resulting in different converted values, as seen in the previous example. Use explicit casting to datetime2 data type whenever a mixed comparison scenario between datetime and datetime2 datatypes exists. For more information, see SQL Server and Azure SQL Database improvements in handling some data types and uncommon operations.

Convert 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 don't specify a style, from date and time types to string literals are 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 causes 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-01-01`.
`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` is 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's 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

CAST and CONVERT (Transact-SQL)

שתף באמצעות

datetime2 (Transact-SQL)

datetime2 description

Supported string literal formats for datetime2

ANSI and ISO 8601 compliance

Backward compatibility for down-level clients

Convert date and time data

Convert other date and time types to the datetime2 data type

Cast explicitly to datetime2 when using datetime

Convert string literals to datetime2

Examples

משוב

משאבים נוספים

שתף באמצעות

datetime2 (Transact-SQL)

datetime2 description

Supported string literal formats for datetime2

ANSI and ISO 8601 compliance

Backward compatibility for down-level clients

Convert date and time data

Convert other date and time types to the datetime2 data type

Cast explicitly to datetime2 when using datetime

Convert string literals to datetime2

Examples

Related content

משוב

משאבים נוספים