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.
For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL). For information and examples that are common to date and time data types and functions, see Using Date and Time Data.
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 of Using Date and Time Data. |
Date range |
0001-01-01 through 9999-12-31 January 1,1 A.D. through December 31, 9999 A.D. |
Time range |
00:00:00 through 23:59:59.9999999 |
Time zone offset range |
|
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. hh is two digits that range from -14 to +14. mm is two digits that range from 00 to 59. |
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 |
Specified scaleResult (precision, scale)Column length (bytes)Fractional seconds precision
datetimeoffset (34,7)107
datetimeoffset(0) (26,0)80-2
datetimeoffset(1) (28,1)80-2
datetimeoffset(2) (29,2)80-2
datetimeoffset(3) (30,3)93-4
datetimeoffset(4) (31,4)93-4
datetimeoffset(5) (32,5)105-7
datetimeoffset(6) (33,6)105-7
datetimeoffset(7) (34,7)105-7
|
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 |
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.
ANSI and ISO 8601 Compliance
The ANSI and ISO 8601 Compliance sections of the date and time topics apply to datetimeoffset.
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'
,CAST('2007-05-08 12:35:29.1234567+12:15' AS datetimeoffset(7)) AS
'datetimeoffset IS08601';
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 |
See Also