• Article

Using XML with time, date, datetime2, and datetimeoffset Data Types

The existing XML schema document sqltypes.xsd describes the W3C XML schema types that are used to describe the SQL Server data types in FOR XML and HTTP/SOAP.

sqltypes.xsd Schema Document

The 2004 XML schema namespace has been extended to include the following SQL Server data types: time, date, datetime2, and datetimeoffset.

.NET Framework System.Data.SqlDbType

The following enumerations are added to SqlDbTypeEnum.

SQL Server type

.NET Framework SqlDbType

date

Date

time

Time

datetime2

LargeDateTime

datetimeoffset

DateTimeWithTimeZone

The complete SqlDbTypeEnum is as follows:

  <xsd:simpleType name="sqlDbTypeEnum">
    <xsd:restriction base="xsd:string">
      <xsd:enumeration value="BigInt" /> 
      <xsd:enumeration value="Binary" /> 
      <xsd:enumeration value="Bit" /> 
      <xsd:enumeration value="Char" /> 
      <xsd:enumeration value="Date" />
      <xsd:enumeration value="DateTime" /> 
      <xsd:enumeration value="DateTimeWithTimeZone" />
      <xsd:enumeration value="Decimal" /> 
      <xsd:enumeration value="Float" /> 
      <xsd:enumeration value="Image" /> 
      <xsd:enumeration value="Int" /> 
      <xsd:enumeration value="LargeDateTime" />
      <xsd:enumeration value="Money" /> 
      <xsd:enumeration value="NChar" /> 
      <xsd:enumeration value="NText" /> 
      <xsd:enumeration value="NVarChar" /> 
      <xsd:enumeration value="Real" /> 
      <xsd:enumeration value="SmallDateTime" /> 
      <xsd:enumeration value="SmallInt" /> 
      <xsd:enumeration value="SmallMoney" /> 
      <xsd:enumeration value="Text" /> 
      <xsd:enumeration value="Time" />
      <xsd:enumeration value="Timestamp" /> 
      <xsd:enumeration value="TinyInt" /> 
      <xsd:enumeration value="Udt" /> 
      <xsd:enumeration value="UniqueIdentifier" /> 
      <xsd:enumeration value="VarBinary" /> 
      <xsd:enumeration value="VarChar" /> 
      <xsd:enumeration value="Variant" /> 
      <xsd:enumeration value="Xml" /> 
    </xsd:restriction>
  </xsd:simpleType>

Description and Mapping of SQL Server Date and Time Data Types

The following table lists the schema definitions for the date and time data types that are new in SQL Server 2008.

Data type

Schema definition

datetime

 
<xsd:simpleType name="datetime">
  <xsd:restriction base="xsd:dateTime">
....<xsd:pattern value="((000[1-9])|(00[1-9][0-9])|(0[1-9][0-....9]{2})|([1-9][0-9]{3}))-((0[1-9])|(1[012]))-((0[1-9])|([12][0-....9])|(3[01]))T(([01][0-9])|(2[0-3]))(:[0-5][0-9]){2}(\.[0-....9]{2}[037])?" /> 
    <xsd:maxInclusive value="9999-12-31T23:59:59.997" /> 
    <xsd:minInclusive value="1753-01-01T00:00:00.000" /> 
  </xsd:restriction>
</xsd:simpleType>

smalldatetime

 
<xsd:simpleType name="smalldatetime">
  <xsd:restriction base="xsd:dateTime">
    <xsd:pattern value="((000[1-9])|(00[1-9][0-9])|(0[1-9][0-9]{2})|([1-9][0-9]{3}))-((0[1-9])|(1[012]))-((0[1-9])|([12][0-9])|(3[01]))T(([01][0-9])|(2[0-3]))(:[0-5][0-9])(:00)" /> 
    <xsd:maxInclusive value="2079-06-06T23:59:00" /> 
    <xsd:minInclusive value="1900-01-01T00:00:00" /> 
  </xsd:restriction>
</xsd:simpleType>

date

 
<xsd:simpleType name="date">
  <xsd:restriction base="xsd:date">
    <xsd:pattern value="((000[1-9])|(00[1-9][0-9])|(0[1-9][0-9]{2})|([1-9][0-9]{3}))-((0[1-9])|(1[012]))-((0[1-9])|([12][0-9])|(3[01]))" /> 
    <xsd:maxInclusive value="9999-12-31" /> 
    <xsd:minInclusive value="0001-01-01" /> 
  </xsd:restriction>
</xsd:simpleType>

time

 
xsd:simpleType name="time">
  <xsd:restriction base="xsd:time">
    <xsd:pattern value="(([01][0-9])|(2[0-3]))(:[0-5][0-9]){2}(\.[0-9]+)?" /> 
    <xsd:annotation>
    <xsd:minInclusive value="00:00:00" /> 
    </xsd:annotation>
  </xsd:restriction>
</xsd:simpleType>

datetime2

 
<xsd:simpleType name="datetime2">
  <xsd:restriction base="xsd:dateTime">
    <xsd:pattern value="((000[1-9])|(00[1-9][0-9])|(0[1-9][0-9]{2})|([1-9][0-9]{3}))-((0[1-9])|(1[012]))-((0[1-9])|([12][0-9])|(3[01]))T(([01][0-9])|(2[0-3]))(:[0-5][0-9]){2}(\.[0-9]+)?" /> 
<xsd:annotation>
  <xsd:minInclusive value="0001-01-01T00:00:00" />
</xsd:annotation>
  </xsd:restriction>
</xsd:simpleType>

datetimeoffset

 
<xsd:simpleType name="DATETIMEOFFSET">
  <xsd:restriction base="xsd:dateTime" />
     <xsd:pattern value="((000[1-9])|(00[1-9][0-9])|(0[1-9][0-9]{2})|([1-9][0-9]{3}))-((0[1-9])|(1[012]))-((0[1-9])|([12][0-9])|(3[01]))T(([01][0-9])|(2[0-3]))(:[0-5][0-9]){2}(\.[0-9]+)?(([\+|\-]((0[0-9])|(1[0-2]))(:[0-5][0-9]))|(\+13(:[0-5][0-9])(:[0-5][0-9]))|\+14:00|Z)"(([\+|\-]((((0[0-9])|(1[0-3]))(:[0-5][0-9]))|14:00))|Z)" /> 
    <xsd:minInclusive value="0001-01-01T00:00:00Z" /> 
  </xsd:restriction>
</xsd:simpleType>

XML Schema Namespace System Catalogs

The new date and time type information that is added in the existing 2004 XML schema namespace is populated to and can be queried from the following XML schema specific system catalogs:

  • Sys.xml_schemla_namespaces

  • Sys.xml_schema_collections

  • Sys.xml_schema_components

XML value() Method

The XML value() method provides a way to indicate the SQL Server data type of the extracted value. This implies a conversion from the XSD value to a SQL Server value for the specified type. The SQL Server date and time types are supported for the following casts:

  • Any XQuery value instance that represents a date can be cast into a date, datetime, smalldatetime, datetime2, datetimeoffset, or any character type in SQL Server.

  • Any XQuery value instance that represents a time can be cast into time, datetime, smalldatetime, datetime2, datetimeoffset or any character type in SQL Server.

  • Any XQuery value instance that represents a date with time and without time zone can be cast into datetime, smalldatetime, datetimeoffet (with 0 zone offset), date (time part being dropped), time (date part being dropped), datetime2, or any character type in SQL Server.

  • Any XQuery value instance that represents a date with time that has a Z or time zone offset (+|-hh:mm) can be cast into datetime (UTC without zone offset), smalldatetime (UTC without zone offset), datetimeoffet, date (time part and zone offset is dropped), datetime2 (UTC without zone offset), time (date part and zone offset is dropped), or any character type in SQL Server.

  • If the cast leads to an overflow or out-of-range condition an error message will be returned.

  • If a date, time or date with time value (with or without time zone) has more precision (fractional seconds) than the target type, the larger fractional seconds precision will be rounded.

  • The date format of any XML value instance that represents a date data type is determined by the SET LANGUAGE and SET DATEFORMAT settings.

Example

The following example uses the value() method.

DECLARE @myDoc xml;
DECLARE @OrderID int;
DECLARE @OrderDate date;
DECLARE @OrderTime time;
DECLARE @OrderDateTime datetimeoffset;
SET @myDoc = '<Root>
<OrderDescription OrderID="1" OrderDate="1999-12-20" OrderTime="13:40:58.47786" OrderDateTime="1999-12-20 13:40:58.123-05:00">
<Features>
  <Warranty>1 year parts and labor</Warranty>
  <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
</Features>
</OrderDescription>
</Root>';

SET @OrderID =  @myDoc.value('(/Root/OrderDescription/@OrderID)[1]', 'int');
SET @OrderDate =  @myDoc.value('(/Root/OrderDescription/@OrderDate)[1]', 'date');
SET @OrderTime =  @myDoc.value('(/Root/OrderDescription/@OrderTime)[1]', 'time');
SET @OrderDateTime =  @myDoc.value('(/Root/OrderDescription/@OrderDateTime)[1]', 'datetimeoffset');

SELECT @OrderID,@OrderDate,@OrderTime,@OrderDateTime;
--Returns: 1           1999-12-20 13:40:58.4778600 1999-12-20 13:40:58.1230000 -0

XQuery Pseudofunctions

sql:column() and sql:variable() recognize the date, time, datetime2, and datetimeoffset data types.

SQL Server to XSD Type Mapping

For sql:variable() and sql:column(), the Xquery base type of the Xquery value that is transformed from the SQL Server value is determined by the SQL Server to XSD type mapping semantic. This mapping semantic is defined in the extended XML schema namespace.

SQL Server type

XSD type

date

xsd:date

time

xsd:time

datetime2

xsd:datetime

datetimeoffset

xsd:datetime

XML storage format type mapping

In SQL Server 2008, the storage format for the XML instance of date and time types changes by using the new SQL Server date and time types. The following table shows the XSD to SQL Server data type mapping. This mapping will also determine the operation semantic between the return results of sql:column() and sql:variable() and XML date and time instances.

XSD type

XML storage SQL Server type mapping with TZ

XML storage SQL Server type mapping without TZ

xs:date

datetimeoffset

date

xs:time

datetimeoffset

datetimeoffset

xs:dateTime

datetimeoffset

datetime2

FOR XML and XMLSCHEMA Clauses

SQL Server columns that are declared by the date, time, datetime2, and datetimeoffset data types are supported when the FOR XML or XMLSCHEMA clause is specified in the SELECT statement.

Output Format

The following table lists FOR XML output formats for the date, time, datetime2, and datetimeoffset data types.

SQL Server type

FOR XML output format

date

YYYY-MM-DD

time

hh:mm:ss[.nnnnnnn]

datetime2

YYYY-MM-DDThh:mm:ss[.nnnnnnn]

datetimeoffset

YYYY-MM-DDThh:mm:ss[.nnnnnnn] [+|-]hh:mm

Example

The following example uses the date, time, anddatetimeoffset types with FOR XML.

CREATE TABLE T1 
    ( 
    dt date, tm time, dtz datetimeoffset
    );
GO
INSERT INTO T1 
VALUES('1996-12-16', '12:30:47.7867', '1996-12-16 12:30:47.7867-05:00');

SELECT dt FROM T1 FOR XML AUTO;
--Returns:
--XML_F52E2B61-18A1-11d1-B105-00805F49916B
------------------------------------------
--<t1 dt="1996-12-16"/>

SELECT tm FROM T1 FOR XML AUTO;
--Returns:
--XML_F52E2B61-18A1-11d1-B105-00805F49916B
------------------------------------------
--<t1 tm="12:30:47.7867"/>

SELECT dtz FROM T1 FOR XML AUTO;
--Returns:
--XML_F52E2B61-18A1-11d1-B105-00805F49916B
------------------------------------------
--<t1 dtz="1996-12-16T12:30:47.7867 -05:00"/>

XSD Inline Schema with the XMLSCHEMA Clause

When the XMLSCHEMA clause is applied with FOR XML clause, the generated XSD inline schema follows the pattern restriction methods that are defined for each of the new date and time types in the extended existing XML schema namespace.

OPENXML () Function

The following table lists the OPENXML input formats for the date, time, datetime2, and datetimeoffset data types.

SQL Server type

FOR XML output format

date

YYYY-MM-DD

time

hh:mm:ss[.nnnnnnn]

datetime2

YYYY-MM-DDThh:mm:ss[.nnnnnnn]

datetimeoffset

YYYY-MM-DDThh:mm:ss[.nnnnnnn][+|-]hh:mm

Example

The following example uses OPENXML with the datetimeoffset data type.

CREATE TABLE T1 
    ( 
    dt date, tm time(7), dtz datetimeoffset(7)
    )
GO
DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(max); -- or xml type
SET @xmlDocument = N'<ROOT>
<T1 dt="2000-08-25" tm="12:30:47.1234567" dtz="2000-08-25T05:22:36.1234567-05:00"/>
</ROOT>';

EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;

-- Use OPENXML to provide rowset that consists of customer data.
INSERT T1
SELECT * 
FROM OPENXML(@docHandle, N'/ROOT/T1') 
    WITH T1;

-- Using OPENXML in a SELECT statement
SELECT * FROM OPENXML(@docHandle, N'/ROOT/T1') WITH (dt date , tm time, dtz datetimeoffset);
EXEC sp_xml_removedocument @docHandle

See Also

Concepts

Using XML Data