TechNet
Export (0) Print
Expand All

datetimeoffset (Transact-SQL)

 

Updated: December 1, 2015

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

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.

PropertyValue
Syntaxdatetimeoffset [ (fractional seconds precision) ]
UsageDECLARE @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 range0001-01-01 through 9999-12-31

January 1, 1 CE through December 31, 9999 CE
Time range00: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 rangesYYYY 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 length26 positions minimum (YYYY-MM-DD hh:mm:ss {+|-}hh:mm) to 34 maximum (YYYY-MM-DD hh:mm:ss.nnnnnnn {+|-}hh:mm)
Precision, scaleSee the table below.
Storage size10 bytes, fixed is the default with the default of 100ns fractional second precision.
Accuracy100 nanoseconds
Default value1900-01-01 00:00:00 00:00
CalendarGregorian
User-defined fractional second precisionYes
Time zone offset aware and preservationYes
Daylight saving awareNo
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

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 8601Description
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.

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

The ANSI and ISO 8601 Compliance sections of the date and time topics apply to datetimeoffset.

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 typeDefault string literal format passed to down-level clientDown-level ODBCDown-level OLEDBDown-level JDBCDown-level SQLCLIENT
timehh:mm:ss[.nnnnnnn]SQL_WVARCHAR or SQL_VARCHARDBTYPE_WSTRor DBTYPE_STRJava.sql.StringString or SqString
dateYYYY-MM-DDSQL_WVARCHAR or SQL_VARCHARDBTYPE_WSTRor DBTYPE_STRJava.sql.StringString or SqString
datetime2YYYY-MM-DD hh:mm:ss[.nnnnnnn]SQL_WVARCHAR or SQL_VARCHARDBTYPE_WSTRor DBTYPE_STRJava.sql.StringString or SqString
datetimeoffsetYYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mmSQL_WVARCHAR or SQL_VARCHARDBTYPE_WSTRor DBTYPE_STRJava.sql.StringString 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)

When converting to
date, the year, month, and day are copied. The following code shows the results of converting a datetimeoffset(4) value to a date value.

DECLARE @datetimeoffset datetimeoffset(4) = '12-10-25 12:32:10 +01:00';  
DECLARE @date date= @datetimeoffset;  
  
SELECT @datetimeoffset AS '@datetimeoffset ', @date AS 'date';  
  
--Result  
--@datetimeoffset                date  
-------------------------------- ----------  
--2025-12-10 12:32:10.0000 +01:0 2025-12-10  
--  
--(1 row(s) affected)  
  

If the conversion is to time(n), the our, minute, second, and fractional seconds are copied. The time zone value is truncated. When the precision of the datetimeoffset(n) value is greater than the precision of the time(n) value, the value is rounded up. The following code shows the results of converting a datetimeoffset(4) value to a time(3) value.

DECLARE @datetimeoffset datetimeoffset(4) = '12-10-25 12:32:10.1237 +01:0';  
DECLARE @time time(3) = @datetimeoffset;  
  
SELECT @datetimeoffset AS '@datetimeoffset ', @time AS 'time';  
  
--Result  
--@datetimeoffset                time  
-------------------------------- ------------  
-- 2025-12-10 12:32:10.1237 +01:00    12:32:10.124  
  
--  
--(1 row(s) affected)  
  

When converting todatetime, the date and time values are copied, and the time zone is truncated. When the fractional precision of the datetimeoffset(n) value is greater than three digits, the value is truncated. The following code shows the results of converting a datetimeoffset(4) value to a datetime value.

DECLARE @datetimeoffset datetimeoffset(4) = '12-10-25 12:32:10.1237 +01:0';  
DECLARE @datetime datetime = @datetimeoffset;  
  
SELECT @datetimeoffset AS '@datetimeoffset ', @datetime AS 'datetime';  
  
--Result  
--@datetimeoffset                datetime  
-------------------------------- -----------------------  
--2025-12-10 12:32:10.1237 +01:0 2025-12-10 12:32:10.123  
--  
--(1 row(s) affected)  

For conversions to
smalldatetime, the date and hours are copied. The minutes are rounded up with respect to the seconds value and seconds are set to 0. The following code shows the results of converting a datetimeoffset(3) value to a smalldatetime value.

DECLARE @datetimeoffset datetimeoffset(3) = '1912-10-25 12:24:32 +10:0';  
DECLARE @smalldatetime smalldatetime = @datetimeoffset;  
  
SELECT @datetimeoffset AS '@datetimeoffset', @smalldatetime AS '@smalldatetime';  
  
--Result  
--@datetimeoffset                @smalldatetime  
-------------------------------- -----------------------  
--1912-10-25 12:24:32.000 +10:00 1912-10-25 12:25:00  
--  
--(1 row(s) affected)  

If the conversion is to datetime2(n), the date and time are copied to the datetime2 value, and the time zone is truncated. When the precision of the datetime2(n) value is greater than the precision of the datetimeoffset(n) value, the fractional seconds are truncated to fit. The following code shows the results of converting a datetimeoffset(4) value to a datetime2(3) value.

DECLARE @datetimeoffset datetimeoffset(4) = '1912-10-25 12:24:32.1277 +10:0';  
DECLARE @datetime2 datetime2(3)=@datetimeoffset;  
  
SELECT @datetimeoffset AS '@datetimeoffset', @datetime2 AS '@datetime2';  
  
--Result  
@datetimeoffset                    @datetime2  
---------------------------------- ----------------------  
1912-10-25 12:24:32.1277 +10:00    1912-10-25 12:24:32.12  
  
--(1 row(s) affected)  

Converting datetimeoffset Data Type to Other Date and Time Types

The following table describes what occurs when a datetimeoffset data type is converted to other date and time data types.

Converting String Literals to datetimeoffset

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 datetimeoffset data type.

Input string literaldatetimeoffset(n)
ODBC DATEODBC string literals are mapped to the datetime data type. Any assignment operation from ODBC DATETIME literals into datetimeoffset types will cause an implicit conversion between datetime and this type as defined by the conversion rules.
ODBC TIMESee previous ODBC DATE rule.
ODBC DATETIMESee previous ODBC DATE rule.
DATE onlyThe TIME part defaults to 00:00:00. The TIMEZONE defaults to +00:00.
TIME onlyThe DATE part defaults to 1900-1-1. The TIMEZONE will default to +00:00.
TIMEZONE onlyDefault values are supplied
DATE + TIMEThe TIMEZONE defaults to +00:00.
DATE + TIMEZONENot allowed
TIME + TIMEZONEThe DATE part defaults to 1900-1-1.
DATE + TIME + TIMEZONETrivial

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 typeOutput
Time12:35:29. 1234567
Date2007-05-08
Smalldatetime2007-05-08 12:35:00
Datetime2007-05-08 12:35:29.123
datetime22007-05-08 12:35:29. 1234567
Datetimeoffset2007-05-08 12:35:29.1234567 +12:15

CAST and CONVERT (Transact-SQL)
AT TIME ZONE (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft