Date data Type

Date:

Defines a date in Sql Server.

Property	Value
Syntax	Date
Usage	DECLARE @MyDate date CREATE TABLE Table1 (Column1 date)
Default string literal format (used for down-level client)	YYYY-MM-DD For more information, see the "Backward Compatibility for Down-level Clients" section that follows.
Range	0001-01-01 through 9999-12-31 January 1, 1 A.D. through December 31, 9999 A.D.
Element ranges	YYYY is four digits from 0001 to 9999 that represent a year. MM is two digits from 01 to 12 that represent a month in the specified year. DD is two digits from 01 to 31, depending on the month, that represent a day of the specified month.
Character length	10 positions
Precision, scale	10, 0
Storage size	3 bytes, fixed
Storage structure	1, 3-byte integer stores date.
Accuracy	One day
Default value	1900-01-01 This value is used for the appended date part for implicit conversion from time to datetime2 or datetimeoffset.
Calendar	Gregorian
User-defined fractional second precision	No
Time zone offset aware and preservation	No
Daylight saving aware	No

Supported String Literal Formats for date

The following tables show the valid string literal formats for the date data type.

Numeric

Description

mdy                          [m]m/dd/[yy]yy        [m]m-dd-[yy]yy        [m]m.dd.[yy]yy         myd                          mm/[yy]yy/dd        mm-[yy]yy/dd        [m]m.[yy]yy.dd        dmy                          dd/[m]m/[yy]yy        dd-[m]m-[yy]yy        dd.[m]m.[yy]yy        dym                          dd/[yy]yy/[m]m        dd-[yy]yy-[m]m        dd.[yy]yy.[m]m        ymd                          [yy]yy/[m]m/dd        [yy]yy-[m]m-dd        [yy]yy-[m]m-dd

[m]m, dd, and [yy]yy represents month, day, and year in a string with slash marks (/), hyphens (-), or periods (.) as separators. Only four- or two-digit years are supported. Use four-digit years whenever possible. To specify an integer from 0001 to 9999 that represents the cutoff year for interpreting two-digit years as four-digit years, use the Configure the two digit year cutoff Server Configuration Option. A two-digit year that is less than or equal to the last two digits of the cutoff year is in the same century as the cutoff year. A two-digit year that is greater than the last two digits of the cutoff year is in the century that comes before the cutoff year. For example, if the two-digit year cutoff is the default 2049, the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950. The default date format is determined by the current language setting. You can change the date format by using the SET LANGUAGE and SET DATEFORMAT statements. The ydm format is not supported for date.

Alphabetical	Description
mon [dd][,] yyyy       mon dd[,] [yy]yy        mon yyyy [dd]           [dd] mon[,] yyyy       dd mon[,][yy]yy        dd [yy]yy mon          [dd] yyyy mon          yyyy mon [dd]          yyyy [dd] mon	Mon represents the full month name or the month abbreviation given in the current language. Commas are optional and capitalization is ignored. To avoid ambiguity, use four-digit years. If the day is missing, the first day of the month is supplied.

ISO 8601	Description
YYYY-MM-DD             YYYYMMDD	Same as the SQL standard. This is the only format that is defined as an international standard.

Unseparated	Description
[yy]yymmdd             yyyy[mm][dd]	The date data can be specified with four, six, or eight digits. A six- or eight-digit string is always interpreted as ymd. The month and day must always be two digits. A four-digit string is interpreted as year.

ANSI and ISO 8601 Compliance:
date complies with the ANSI SQL standard definition for the Gregorian calendar: "NOTE 85 - Datetime data types will allow dates in the Gregorian format to be stored in the date range 0001–01–01 CE through 9999–12–31 CE."
The default string literal format, which is used for down-level clients, complies with the SQL standard form which is defined as YYYY-MM-DD. This format is the same as the ISO 8601 definition for DATE.

ISO international standard:

Examples:

DECLARE @d DATE

SELECT @d = '20170306'

select CONVERT(VARCHAR(11),@d,106)

Output: 06 Mar 2017

This date is compared with “YYYYMMDD” because of no separators and it returns correct date.

DECLARE @d DATE

SELECT @d = '03062017'

select CONVERT(VARCHAR(11),@d,106)

Msg 241, Level 16, State 1, Line 4

Conversion failed when converting date and/or time from character string.

This date interpreted as

0306 as YYYY

20      as MM   (20 for MM is invalid so we got conversion error.)

17      as DD

DECLARE @d DATE

SELECT @d = '03060217'

select CONVERT(VARCHAR(11),@d,106)

Output: 17 Feb 0306

Month abbreviation

Examples:

DECLARE @d DATE

SELECT @d = 'Mar 03 2017'

select CONVERT(VARCHAR(11),@d,106)

Output: 03 Mar 2017

The space is mandatory between Mar and date and year part.

DECLARE @d DATE

SELECT @d = 'Mar 03 17'

select CONVERT(VARCHAR(11),@d,106)

Output: 03 Mar 2017

DECLARE @d DATE

SELECT @d = 'Mar 17 03'

select CONVERT(VARCHAR(11),@d,106)

Output: 17 Mar 2003

The date is interpreted as “mdy”. So 17 is interpreted as date and 03 as two part year.

String Literal Formats:

Examples

DECLARE @d DATE

SELECT @d = '03.06.2017'

select CONVERT(VARCHAR(11),@d,106)

Output:  06 Mar 2017

The date is interpreted as “mdy”

DECLARE @d DATE

SELECT @d = '06.03.2017'

select CONVERT(VARCHAR(11),@d,106)

Output:  03 Jun 2017

The date is interpreted as “mdy”

Datetime:

Defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock.

Note:

Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision.datetimeoffset provides time zone support for globally deployed applications.

Datetime Description

Property	Value
Syntax	datetime
Usage	DECLARE @MyDatetime datetime CREATE TABLE Table1 ( Column1 datetime )
Default string literal formats (used for down-level client)	Not applicable
Date range	January 1, 1753, through December 31, 9999
Time range	00:00:00 through 23:59:59.997
Time zone offset range	None
Element ranges	YYYY is four digits from 1753 through 9999 that represent a year. MM is two digits, ranging from 01 to 12, that represent a month in the specified year. DD is two digits, ranging from 01 to 31 depending on the month, that represent a day of the specified month. hh is two digits, ranging from 00 to 23, that represent the hour. mm is two digits, ranging from 00 to 59, that represent the minute. ss is two digits, ranging from 00 to 59, that represent the second. n* is zero to three digits, ranging from 0 to 999, that represent the fractional seconds.
Character length	19 positions minimum to 23 maximum
Storage size	8 bytes 2 integers, 4-byte integer stores date. 4-byte integer stores time.
Accuracy	Rounded to increments of .000, .003, or .007 seconds
Default value	1900-01-01 00:00:00
Calendar	Gregorian (Does not include the complete range of years.)
User-defined fractional second precision	No
Time zone offset aware and preservation	No
Daylight saving aware	No

Supported String Literal Formats for datetime:
The following tables list the supported string literal formats for datetime. Except for ODBC, datetime string literals are in single quotation marks ('), for example, 'string_literaL'. If the environment is not us_english, the string literals should be in the format N'string_literaL'.

Numeric

Description

Date formats: [0]4/15/[19]96 -- (mdy) [0]4-15-[19]96 -- (mdy) [0]4.15.[19]96 -- (mdy) [0]4/[19]96/15 -- (myd) 15/[0]4/[19]96 -- (dmy) 15/[19]96/[0]4 -- (dym) [19]96/15/[0]4 -- (ydm) [19]96/[0]4/15 -- (ymd) Time formats: 14:30 14:30[:20:999] 14:30[:20.9] 4am 4 PM

You can specify date data with a numeric month specified. For example, 5/20/97 represents the twentieth day of May 1997. When you use numeric date format, specify the month, day, and year in a string that uses slash marks (/), hyphens (-), or periods (.) as separators. This string must appear in the following form:  number separator number separator number [time] [time] When the language is set to us_english, the default order for the date is mdy. You can change the date order by using the SET DATEFORMAT statement. The setting for SET DATEFORMAT determines how date values are interpreted. If the order does not match the setting, the values are not interpreted as dates, because they are out of range or the values are misinterpreted. For example, 12/10/08 can be interpreted as one of six dates, depending on the DATEFORMAT setting. A four-part year is interpreted as the year.

Alphabetical

Description

Apr[il] [15][,] 1996 Apr[il] 15[,] [19]96 Apr[il] 1996 [15] [15] Apr[il][,] 1996 15 Apr[il][,][19]96 15 [19]96 apr[il] [15] 1996 apr[il] 1996 APR[IL] [15] 1996 [15] APR[IL]

You can specify date data with a month specified as the full month name. For example, April or the month abbreviation of Apr specified in the current language; commas are optional and capitalization is ignored. Here are some guidelines for using alphabetical date formats: 1) Enclose the date and time data in single quotation marks ('). For languages other than English, use N' 2) Characters that are enclosed in brackets are optional. 3) If you specify only the last two digits of the year, values less than the last two digits of the value of the Configure the two digit year cutoff Server Configuration Option configuration option are in the same century as the cutoff year. Values greater than or equal to the value of this option are in the century that comes before the cutoff year. For example, if two digit year cutoff is 2050 (default), 25 is interpreted as 2025 and 50 is interpreted as 1950. To avoid ambiguity, use four-digit years. 4) If the day is missing, the first day of the month is supplied. The SET DATEFORMAT session setting is not applied when you specify the month in alphabetical form.

ISO 8601

Description

YYYY-MM-DDThh:mm:ss[.mmm] YYYYMMDD[ hh:mm:ss[.mmm]]

Examples: 1) 2004-05-23T14:25:10 2) 2004-05-23T14:25:10.487 To use the ISO 8601 format, you must specify each element in the format. This also includes the T, the colons (:), and the period (.) that are shown in the format. The brackets indicate that the fraction of second component is optional. The time component is specified in the 24-hour format. The T indicates the start of the time part of the datetime value. The advantage in using the ISO 8601 format is that it is an international standard with unambiguous specification. Also, this format is not affected by the SET DATEFORMAT or SET LANGUAGE setting.

Rounding of datetime Fractional Second Precision:
datetime values are rounded to increments of .000, .003, or .007 seconds, as shown in the following table.

User-specified value	System stored value
01/01/98 23:59:59.999	1998-01-02 00:00:00.000
01/01/98 23:59:59.995 01/01/98 23:59:59.996 01/01/98 23:59:59.997 01/01/98 23:59:59.998	1998-01-01 23:59:59.997
01/01/98 23:59:59.992 01/01/98 23:59:59.993 01/01/98 23:59:59.994	1998-01-01 23:59:59.993
01/01/98 23:59:59.990 01/01/98 23:59:59.991	1998-01-01 23:59:59.990

Last Digit	Rounded value
0 or 1	0
2,3 or 4	3
5,6,7 or 8	7
9	0 ( increase previous digit)

ANSI and ISO 8601 Compliance

Datetime is not ANSI or ISO 8601 compliant.

time (Transact-SQL)
Defines a time of a day. The time is without time zone awareness and is based on a 24-hour clock.
Time Description

Property	Value
Syntax	time [ (fractional second precision) ]
Usage	DECLARE @MyTime time(7) CREATE TABLE Table1 ( Column1 time(7) )
fractional seconds precision	Specifies the number of digits for the fractional part of the seconds. This can be an integer from 0 to 7. For Informatica, this can be an integer from 0 to 3. The default fractional precision is 7 (100ns).
Default string literal format (used for down-level client)	hh:mm:ss[.nnnnnnn] (hh:mm:ss[.nnn] for Informatica) For more information, see the "Backward Compatibility for Down-level Clients" section that follows..
Range	00:00:00.0000000 through 23:59:59.9999999 (00:00:00.000 through 23:59:59.999 for Informatica)
Element ranges	hh is two digits, ranging from 0 to 23, that represent the hour. mm is two digits, ranging from 0 to 59, that represent the minute. ss is two digits, ranging from 0 to 59, that represent the second. n* is zero to seven digits, ranging from 0 to 9999999, that represent the fractional seconds. For Informatica, n* is zero to three digits, ranging from 0 to 999.
Character length	8 positions minimum (hh:mm:ss) to 16 maximum (hh:mm:ss.nnnnnnn). For Informatica, the maximum is 12 (hh:mm:ss.nnn).
Precision, scale (user specifies scale only)	See the table below.
Storage size	5 bytes, fixed, is the default with the default of 100ns fractional second precision. In Informatica, the default is 4 bytes, fixed, with the default of 1ms fractional second precision.
Accuracy	100 nanoseconds (1 millisecond in Informatica)
Default value	00:00:00 This value is used for the appended time part for implicit conversion from date to datetime2 or datetimeoffset.
User-defined fractional second precision	Yes
Time zone offset aware and preservation	No
Daylight saving aware	No

Specified scale	Result (precision, scale)	Column length (bytes)	Fractional seconds precision
time	(16,7) [(12,3) in Informatica]	5 (4 in Informatica)	7 (3 in Informatica)
time(0)	(8,0)	3	0-2
time(1)	(10,1)	3	0-2
time(2)	(11,2)	3	0-2
time(3)	(12,3)	4	3-4
time(4) Not supported in Informatica.	(13,4)	4	3-4
time(5) Not supported in Informatica.	(14,5)	5	5-7
time(6) Not supported in Informatica.	(15,6)	5	5-7
time(7) Not supported in Informatica.	(16,7)	5	5-7

Supported String Literal Formats for time:
The following table shows the valid string literal formats for the time data type.

SQL Server	Description
hh:mm[:ss][:fractional seconds][AM][PM] hh:mm[:ss][.fractional seconds][AM][PM] hhAM[PM] hh AM[PM]	The hour value of 0 represents the hour after midnight (AM), regardless of whether AM is specified. PM cannot be specified when the hour equals 0. Hour values from 01 through 11 represent the hours before noon if neither AM nor PM is specified. The values represent the hours before noon when AM is specified. The values represent hours after noon if PM is specified. The hour value 12 represents the hour that starts at noon if neither AM nor PM is specified. If AM is specified, the value represents the hour that starts at midnight. If PM is specified, the value represents the hour that starts at noon. For example, 12:01 is 1 minute after noon, as is 12:01 PM; and 12:01 AM is one minute after midnight. Specifying 12:01 AM is the same as specifying 00:01 or 00:01 AM. Hour values from 13 through 23 represent hours after noon if AM or PM is not specified. The values also represent the hours after noon when PM is specified. AM cannot be specified when the hour value is from 13 through 23. An hour value of 24 is not valid. To represent midnight, use 12:00 AM or 00:00. Milliseconds can be preceded by either a colon (:) or a period (.). If a colon is used, the number means thousandths-of-a-second. If a period is used, a single digit means tenths-of-a-second, two digits mean hundredths-of-a-second, and three digits mean thousandths-of-a-second. For example, 12:30:20:1 indicates 20 and one-thousandth seconds past 12:30; 12:30:20.1 indicates 20 and one-tenth seconds past 12:30.
ISO 8601	Notes
hh:mm:ss hh:mm[:ss][.fractional seconds]	hh is two digits, ranging from 0 to 14, that represent the number of hours in the time zone offset. mm is two digits, ranging from 0 to 59, that represent the number of additional minutes in the time zone offset.

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 existingdatetime 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 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, 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. In Informatica, the fractional seconds will be truncated when n > 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 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'.

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

Smalldatetime (Transact-SQL):
Defines a date that is combined with a time of day. The time is based on a 24-hour day, with seconds always zero (:00) and without fractional seconds.
Note: Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable.time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.
Smalldatetime Description:

Property	Value
Syntax	smalldatetime
Usage	DECLARE @MySmalldatetime smalldatetime CREATE TABLE Table1 ( Column1 smalldatetime )
Default string literal formats (used for down-level client)	Not applicable
Date range	1900-01-01 through 2079-06-06 January 1, 1900, through June 6, 2079
Time range	00:00:00 through 23:59:59 2007-05-09 23:59:59 will round to 2007-05-10 00:00:00
Element ranges	YYYY is four digits, ranging from 1900, to 2079, that represent a year. MM is two digits, ranging from 01 to 12, that represent a month in the specified year. DD is two digits, ranging from 01 to 31 depending on the month, that represent a day of the specified month. hh is two digits, ranging from 00 to 23, that represent the hour. mm is two digits, ranging from 00 to 59, that represent the minute. ss is two digits, ranging from 00 to 59, that represent the second. Values that are 29.998 seconds or less are rounded down to the nearest minute, Values of 29.999 seconds or more are rounded up to the nearest minute.
Character length	19 positions maximum
Storage size	4 bytes, fixed.
Accuracy	One minute
Default value	1900-01-01 00:00:00
Calendar	Gregorian (Does not include the complete range of years.)
User-defined fractional second precision	No
Time zone offset aware and preservation	No
Daylight saving aware	No

ANSI and ISO 8601 Compliance

Smalldatetime is not ANSI or ISO 8601 compliant.

Casting string literals with seconds to smalldatetime
The following example compares the conversion of seconds in string literals to smalldatetime.
SELECT  
     CAST('2007-05-08 12:35:29'     AS smalldatetime) 
    ,CAST('2007-05-08 12:35:30'     AS smalldatetime) 
    ,CAST('2007-05-08 12:59:59.998' AS smalldatetime); 

Input	Output
2007-05-08 12:35:29	2007-05-08 12:35:00
2007-05-08 12:35:30	2007-05-08 12:36:00
2007-05-08 12:59:59.998	2007-05-08 13:00:00

Datetimeoffset (Transact-SQL):
Defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock.
Datetimeoffset Description:

Property	Value
Syntax	datetimeoffset [ (fractional seconds precision) ]
Usage	DECLARE @MyDatetimeoffset datetimeoffset(7) CREATE TABLE Table1 ( Column1 datetimeoffset(7) )
Default string literal formats (used for down-level client)	YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm] 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 CE through December 31, 9999 CE
Time range	00:00:00 through 23:59:59.9999999 (fractional seconds are not supported in Informatica)
Time zone offset range	-14:00 through +14:00 (the time zone offset is ignored in Informatica)
Element ranges	YYYY is four digits, ranging from 0001 through 9999, that represent a year. MM is two digits, ranging from 01 to 12, that represent a month in the specified year. DD is two digits, ranging from 01 to 31 depending on the month, that represent a day of the specified month. hh is two digits, ranging from 00 to 23, that represent the hour. mm is two digits, ranging from 00 to 59, that represent the minute. ss is two digits, ranging from 00 to 59, that represent the second. n* is zero to seven digits, ranging from 0 to 9999999, that represent the fractional seconds. Fractional seconds are not supported in Informatica. hh is two digits that range from -14 to +14. The time zone offset is ignored in Informatica. mm is two digits that range from 00 to 59. The time zone offset is ignored in Informatica.
Character length	26 positions minimum (YYYY-MM-DD hh:mm:ss {+|-}hh:mm) to 34 maximum (YYYY-MM-DD hh:mm:ss.nnnnnnn {+|-}hh:mm)
Precision, scale	See the table below.
Storage size	10 bytes, fixed is the default with the default of 100ns fractional second precision.
Accuracy	100 nanoseconds
Default value	1900-01-01 00:00:00 00:00
Calendar	Gregorian
User-defined fractional second precision	Yes
Time zone offset aware and preservation	Yes
Daylight saving aware	No

Specified scale	Result (precision, scale)	Column length (bytes)	Fractional seconds precision
datetimeoffset	(34,7)	10	7
datetimeoffset(0)	(26,0)	8	0-2
datetimeoffset(1)	(28,1)	8	0-2
datetimeoffset(2)	(29,2)	8	0-2
datetimeoffset(3)	(30,3)	9	3-4
datetimeoffset(4)	(31,4)	9	3-4
datetimeoffset(5)	(32,5)	10	5-7
datetimeoffset(6)	(33,6)	10	5-7
datetimeoffset(7)	(34,7)	10	5-7

Supported String Literal Formats for datetimeoffset
The following table lists the supported ISO 8601 string literal formats for datetimeoffset. For information about alphabetical, numeric, unseparated and time formats for the date and time parts of datetimeoffset, see date (Transact-SQL) and time (Transact-SQL).

ISO 8601	Description
YYYY-MM-DDThh:mm:ss[.nnnnnnn][{+|-}hh:mm]	These two formats are not affected by the SET LANGUAGE and SET DATEFORMAT session locale settings. Spaces are not allowed between the datetimeoffset and the datetime parts.
YYYY-MM-DDThh:mm:ss[.nnnnnnn]Z (UTC)	This format by ISO definition indicates the datetime portion should be expressed in Coordinated Universal Time (UTC). For example, 1999-12-12 12:30:30.12345 -07:00 should be represented as 1999-12-12 19:30:30.12345Z.

Time Zone Offset
A time zone offset specifies the zone offset from UTC for a time or datetime value. The time zone offset can be represented as [+|-] hh:mm:
hh is two digits that range from 00 to 14 and represent the number of hours in the time zone offset.
mm is two digits, ranging from 00 to 59, that represent the number of additional minutes in the time zone offset.
+ (plus) or – (minus) is the mandatory sign for a time zone offset. This indicates whether the time zone offset is added or subtracted from the UTC time to obtain the local time. The valid range of time zone offset is from -14:00 to +14:00.
The time zone offset range follows the W3C XML standard for XSD schema definition and is slightly different from the SQL 2003 standard definition, 12:59 to +14:00.
The optional type parameter fractional seconds precision specifies the number of digits for the fractional part of the seconds. This value can be an integer with 0 to 7 (100 nanoseconds). The default fractional seconds precision is 100ns (seven digits for the fractional part of the seconds).
The data is stored in the database and processed, compared, sorted, and indexed in the server as in UTC. The time zone offset will be preserved in the database for retrieval.
The given time zone offset will be assumed to be daylight saving time (DST) aware and adjusted for any given datetime that is in the DST period.
For datetimeoffset type, both UTC and local (to the persistent or converted time zone offset) datetime value will be validated during insert, update, arithmetic, convert, or assign operations. The detection of any invalid UTC or local (to the persistent or converted time zone offset) datetime value will raise an invalid value error. For example, 9999-12-31 10:10:00 is valid in UTC, but overflow in local time to the time zone offset +13:50.
To convert a date to a corresponding datetimeoffset value in a target time zone, see AT TIME ZONE (Transact-SQL).
ANSI and ISO 8601 Compliance
The ANSI and ISO 8601 Compliance sections of the date and time topics apply to datetimeoffset.
Detailed Summary:

Data Type	Value
Date	Date Range      :   0001-01-01 through 9999-12-31 Time Range      :   NA Accuracy           :    One day                             Default                 :    1900-01-01 Storage size     :     3 bytes, fixed ANSI and ISO 8601 Compliance : YES
Datetime	Date Range      :   January 1, 1753, through December 31, 9999 Time Range      :   00:00:00 through 23:59:59.997 Accuracy           :   Rounded to increments of .000, .003, or .007 seconds                             Default              :    1900-01-01 00:00:00 Storage size     :     8 bytes ANSI and ISO 8601 Compliance : No
datetime2 [ (fractional seconds precision) ]	Date Range      :   0001-01-01 through 9999-12-31 Time Range      :   00:00:00 through 23:59:59.9999999 Accuracy           :   100 nanoseconds                             Default              :    1900-01-01 00:00:00 Storage size     :     depends on precision ANSI and ISO 8601 Compliance : YES
Smalldatetime	Date Range      :   1900-01-01 through 2079-06-06 Time Range      :   00:00:00 through 23:59:59 Accuracy           :   One minute                             Default              :    1900-01-01 00:00:00 Storage size     :     4 bytes, fixed ANSI and ISO 8601 Compliance : No
datetimeoffset [ (fractional seconds precision) ]	Date Range      :   0001-01-01 through 9999-12-31 Time Range      :   00:00:00 through 23:59:59.9999999 Time zone offset range: -14:00 through +14:00 Accuracy           :   100 nanoseconds                             Default              :   1900-01-01 00:00:00 00:00 Storage size     :     depends on precision ANSI and ISO 8601 Compliance : YES
time [ (fractional second precision) ]	Date Range      :   NA Time Range      :   00:00:00.0000000 through 23:59:59.9999999 Time zone offset range: NA Accuracy           :   100 nanoseconds                             Default              :   00:00:00 Storage size     :     5 bytes Fixed ANSI and ISO 8601 Compliance : YES

Date To Time
When the conversion is to time(n) from Date, the conversion fails, and error message 206 is raised: "Operand type clash: date is incompatible with time".
Date To DateTime
If the conversion is to datetime, the date is copied and the time component is set to 00:00:00.000.
Date To SmalldateTime
In the case of conversion to smalldatetime, when the date value is in the range of a smalldatetime, the date component is copied and the time component is set to 00:00:00. When the date value is outside the range of a smalldatetime value, error message 242 is raised: "The conversion of adate data type to a smalldatetime data type results in an out-of-range value;and the smalldatetime value is set to NULL.
Date To DateTimeoffset(n)
When the conversion is to datetimeoffset(n), the date is copied, and the time is set to 00:00.0000000 +00:00 depends on offset.
Date To DateTime2(n)
If the conversion is to datetime2(n), the date component is copied, and the time component is set to 00:00:00.00 regardless of the value of (n)