Convert xml datetime (xs:dateTime) to SQL datetime

/*

SQL Server 2005 supports xquery as the DML on xml data. The value() method is used to retrieve the value of attributes and element of xml in SQL Server 2005. However, the datetime data type in xml xs:dateTime is different than in SQL server and SQL server CONVERT command does not support the conversion from xml datetime to SQL datetime data type.

In xml, date time data type is defined as:

1) YYYY-MM-DDTHH:MI:SS.MMMZ, MMM is millisecond and Z is for zulu time zone. e.g. 2007-01-01T10:10:10Z.

2) YYYY-MM-DDTHH:MI:SS.MMMTZ, TZ is the time zone and the time part is UTC time. e.g.: 2007-01-01T10:10:10-5:00 or 2007-01-01T10:10:10+3:00

For a time of UTC -5:00 time zone, if the local time is 10:00:00 of 2007-01-01, you can use "2007-01-01T10:00:00Z" or

"2007-01-01T15:00:00.000-5:00" to represent it in xml.

Now let’s talk about the conversion of xml datetime to SQL datetime.

*/

declare @xml xml,@xml2 xml;

set @xml=‘<Root><SaleDate>2007-01-01T10:00:00.24-05:00</SaleDate></Root>’

set

@xml2=‘<Root><SaleDate>2007-01-01T10:00:00.24Z</SaleDate></Root>’

— We can get the Sale Date as string:

select

@xml.value(‘/Root[1]/SaleDate[1]’,‘nvarchar(100)’) AS strDateTime

select @xml2.value(‘/Root[1]/SaleDate[1]’,‘nvarchar(100)’) AS strDateTime2

— But if we try to get the Sale date as datetime, the first one fails and the second one succeeded.

— This is becuase SQL server does not support date time with time zone definition.

/*

Fail:

Msg 241, Level 16, State 1, Line 10

Conversion failed when converting datetime from character string.

select @xml.value(‘/Root[1]/SaleDate[1]’,’datetime’)

*/

— Works:

select @xml2.value(‘/Root[1]/SaleDate[1]’,‘datetime’) AS dtDateTime2

/*

SQL server CONVERT command has 126 and 127 format, which supports yyyy-mm-ddThh:ms:ss.mmm and yyyy-mm-ddThh:ms:ssZ format respectively. Now let’s try to use these two format:

*/

/*

— 126, yyyy-mm-ddThh:mi:ss.mmm, fails on both xml because the format does not match.

select convert(datetime,@xml.value(‘/Root[1]/SaleDate[1]’,’nvarchar(100)’), 126)

select convert(datetime,@xml2.value(‘/Root[1]/SaleDate[1]’,’nvarchar(100)’), 126)

— 127, yyyy-mm-ddThh:mi:ss.mmmZ, fails on the first one because the format does not match.

select convert(datetime,@xml.value(‘/Root[1]/SaleDate[1]’,’nvarchar(100)’), 127)

*/

–Format 127 works on the second xml:

select convert(datetime,@xml2.value(‘/Root[1]/SaleDate[1]’,‘nvarchar(100)’), 127) AS dtDateTime2

— To correctly convert xml datetime regardless its format to SQL data time, we need to use xquery function

— xs:dateTime:

select

@xml.value(xs:dateTime(/Root[1]/SaleDate[1])’,‘datetime’) AS dtDateTime

select

@xml2.value(xs:dateTime(/Root[1]/SaleDate[1])’,‘datetime’) AS dtDateTime2

— Both works fine, and the xs:dateTime function converts the time to local time based on the time zone.

— The first xml returns 2007-01-01 15:00:00.240 and the second returns 2007-01-01 10:00:00.240.

 

Advertisements
This entry was posted in SQL server 2005. Bookmark the permalink.

3 Responses to Convert xml datetime (xs:dateTime) to SQL datetime

  1. Joel says:

    Now if you could just get Microsoft to document Format 127 in BOL half as well as you\’ve explained it here!
     
    Thanks, you sure saved me a huge headache here!

  2. Ant says:

    Very useful – thanks.One annoying thing is that the value() function comes back with NULL if no time zone is specified. This is not very good for dealing with unpredictable data. The custom function below works with time zone and no time zone.Any suggestions for making this more efficient?<Aside><Rant>XML has solved the age-old problems of date representation in documents, but nobody seems to have ever implemented the XML date formats correctly</Rant></Aside>CREATE FUNCTION [XmlDateTimeToSqlDateTime] ( @xmlDateTime nvarchar(50))RETURNS datetimeASBEGIN — Declare the return variable here DECLARE @dateConvert xml DECLARE @ret datetime SET @dateConvert = @xmlDateTime SET @ret = ( SELECT isnull(@dateConvert.value(\’xs:dateTime(.)\’, \’datetime\’), convert(datetime, @xmlDateTime, 127)) ) RETURN @retEND

  3. awhite says:

    Very useful, indeed. Thank you for taking the time to post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s