Datetime2 - TSQL Tutorial

On Transact SQL language the datetime2 is part of date and time data types and is an extension of the datetime type that has a larger date range, a larger default fractional precision.

Datetime2 syntax:

datetime2

PropertyValue
Default string literal formatYYYY-MM-DD hh:mm:ss[.fractional seconds]
Range0001-01-01 through 9999-12-31
Length19 minimum - 27 maximum
Storage size8 bytes
CalendarGregorian

Datetime2 example:

USE model;
GO
DECLARE @datetime2 datetime2 = '08-21-14 10:09:30.123';
SELECT @datetime2 AS 'Datetime2';
GO

Datetime2
2014-08-21 10:09:30.1230000