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.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.

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

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

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

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

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 time component is set to 00:00:00.000.

The following code shows the results of converting a date value to a datetime value.

DECLARE @date date = '12-21-05';
DECLARE @datetime datetime = @date;

SELECT @datetime AS '@datetime', @date AS '@date';

--Result
--@datetime               @date
------------------------- ----------
--2005-12-21 00:00:00.000 2005-12-21

time(n)

The time component is copied, and the date component is set to '1900-01-01'. When the fractional precision of the time(n) value greater than three digits, the value will be truncated to fit.

The following example shows the results of converting a time(4) value to a datetime value.

DECLARE @time time(4) = '12:10:05.1237';
DECLARE @datetime datetime = @time;

SELECT @datetime AS '@datetime', @time AS '@time';

--Result
--@datetime               @time
------------------------- -------------
--1900-01-01 12:10:05.123 12:10:05.1237
--
--(1 row(s) affected)

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 datetime value.

DECLARE @smalldatetime smalldatetime = '12-01-01 12:32';
DECLARE @datetime datetime = @smalldatetime;

SELECT @datetime AS '@datetime', @smalldatetime AS '@smalldatetime';

--Result
--@datetime               @smalldatetime
------------------------- -----------------------
--2001-12-01 12:32:00.000 2001-12-01 12:32:00
--
--(1 row(s) affected)

datetimeoffset(n)

The date and time components are copied. The time zone is truncated. When the fractional precision of the datetimeoffset(n) value is greater than three digits, the value will be truncated.

The following example shows the results of converting a datetimeoffset(4) value to a datetime value.

DECLARE @datetimeoffset datetimeoffset(4) = '1968-10-23 12:45:37.1234 +10:0';
DECLARE @datetime datetime = @datetimeoffset;

SELECT @datetime AS '@datetime', @datetimeoffset AS '@datetimeoffset';

--Result
--@datetime               @datetimeoffset
------------------------- ------------------------------
--1968-10-23 12:45:37.123 1968-10-23 12:45:37.1237 +01:0
--
--(1 row(s) affected)

datetime2(n)

The date and time are copied. When the fractional precision of the datetime2(n) value is greater than three digits, the value will be truncated.

The following example shows the results of converting a datetime2(4) value to a datetime value.

DECLARE @datetime2 datetime2(4) = '1968-10-23 12:45:37.1237';
DECLARE @datetime datetime = @datetime2;

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

--Result
--@datetime               @datetime2
------------------------- ------------------------
--1968-10-23 12:45:37.123 1968-10-23 12:45:37.1237
--
--(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.

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

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

Community Additions

ADD
Show:
© 2014 Microsoft