Date and time datatypes in SQL Server 2012

SQL Server 2012  provides following date and time datatypes:
date, time, datetime, datetime2, datetimeoffset, smalldatetime.

Datatype date stored date in YYYY-MM-DD default format. It is string literal fromat. Character length this type is 10 precisions. Its storage size is 3 bytes.  Default value is 1900-01-01.

Datatype time stored timeof a day in hh-mm-ss[.nnnnnnn] default format based on a 24-hour clock. It is string literal fromat. Character length this type is 8-16 positions. Its storage size is 5 bytes.  Default value is 00:00:00. Fractional seconds precision can be an integer from 0 to 7.

time and time(7) – has 16 precisions with 7 fractional precisions, column length is 5 bytes,
time(0) – has  8 precisions with 0 fractional precisions and 0-2 fractional seconds precisions, column length is 3 bytes,
time(1) – has 10 precisions with 1 fractional precisions and 0-2 fractional seconds precisions, column length is 3 bytes,
time(2) – has 11 precisions with 2 fractional precisions and 0-2 fractional seconds precisions, column length is 3 bytes,
time(3) – has 12 precisions with 3 fractional precisions and 3-4 fractional seconds precisions, column length is 4 bytes,
time(4) – has 13 precisions with 4 fractional precisions and 3-4 fractional seconds precisions, column length is 4 bytes,
time(5) – has 14 precisions with 5 fractional precisions and 5-7 fractional seconds precisions, column length is 5 bytes,
time(6) – has 15 precisions with 6 fractional precisions and 5-7 fractional seconds precisions, column length is 5 bytes.

It is my comparison for diffrences in these types of time data:

create table tab
(
v_time time not null, v_time0 time(0) not null,
v_time1 time(1) not null, v_time2 time(2) not null,
v_time3 time(3) not null, v_time4 time(4) not null,
v_time5 time(5) not null,v_time6 time(6) not null,
v_time7 time(7) not null,
);

insert into tab values ( 
  SYSDATETIMEOFFSET(), SYSDATETIMEOFFSET(), SYSDATETIMEOFFSET() 
 ,SYSDATETIMEOFFSET(), SYSDATETIMEOFFSET(), SYSDATETIMEOFFSET() 
 ,SYSDATETIMEOFFSET(), SYSDATETIMEOFFSET() , SYSDATETIMEOFFSET() 
);

a11

Datatype smalldatetime stored date and time: date from 1900-01-01 through 2079-06-06 and time from 00:00:00 through 23:59:59. It is string literal fromat YYYY-MM-DD hh-mm-ss. Character length this type is 19-23 precisions. Rounded to increments of .000, .003, or .007 seconds. Its storage size is 8 bytes.  Default value is 1900-01-01 00:00:00.

Datatype datetime stored date and time: date from January 1, 1753 through December 31, 9999 and time from 00:00:00 through 23:59:59.997. It is string literal fromat YYYY-MM-DD hh-mm-ss[.nnn]. Character length this type is 19 max precisions. Its storage size is 4 bytes.  Default value is 1900-01-01 00:00:00.

Datatype datetime2 stored date and time: date from 0001-01-01 through 9999-12-31 and time from 00:00:00 through 23:59:59.9999999. It is string literal fromat: YYYY-MM-DD hh-mm-ss[.nnnnnnn]. Character length this type is 19-27 precisions, default is 7 precisions. Its storage size is 6( for precisions less than 3) – 8( for 5 and more precisions ) bytes.  Default value is 1900-01-01 00:00:00.

Datatype datetimeoffset stored date and time: date from 0001-01-01 through 9999-12-31 and time from 00:00:00 through 23:59:59.9999999 and time zone from  -14:00 through +14:00. It is string literal fromat YYYY-MM-DD hh-mm-ss[.nnnnnnn][+|-]HH:mm]. Character length this type is 26-34 precisions. Accuracy is 100 ns. Its storage size is 10 bytes.  Default value is 1900-01-01 00:00:00 00:00.

datetimeoffset and datetimeoffset(34,7) – has 34 precisions with 7 fractional precisions
and  5-7 fractional seconds precisions  default is 7, column length is 10 bytes,
datetimeoffset(26,0) – has 26 precisions with 0 fractional precisions
and 0-2 fractional seconds precisions, column length is 8 bytes,
datetimeoffset(28,1) – has 28 precisions with 1 fractional precisions
and 0-2 fractional seconds precisions, column length is 8 bytes,
datetimeoffset(29,2) – has 29 precisions with 2 fractional precisions
and 0-2 fractional seconds precisions, column length is 8 bytes,
datetimeoffset(30,3) – has 30 precisions with 3 fractional precisions
and 3-4 fractional seconds precisions, column length is 9 bytes,
datetimeoffset(31,4) – has 31 precisions with 4 fractional precisions
and 3-4 fractional seconds precisions, column length is 9 bytes,
datetimeoffset(32,5) – has 32 precisions with 5 fractional precisions
and 5-7 fractional seconds precisions, column length is 10 bytes,
datetimeoffset(33,6) – has 33 precisions with 6 fractional precisions
and 5-7 fractional seconds precisions, column length is 10 bytes.

It is my comparison for diffrences in datetime datatypes:

create table tabd
(
  v_date date not null, 
  v_datetime datetime not null,
  v_datetime2 datetime2 not null, 
  v_datetimeoffset datetimeoffset not null
);

insert into tabd values ( 
  SYSDATETIMEOFFSET() , SYSDATETIMEOFFSET() 
, SYSDATETIMEOFFSET() , SYSDATETIMEOFFSET() 
);

a111Choice of  date and time datatype should be adjust to need of stored values.  If the value must stored time zone You should use datetimeoffset datatype, but in other case it will be to enough another date and time datatype of less byte size. In this way the database will be take less disk size.