Converting XML Datetime to SQL Datetime

I have an XML colmn in SQL 2005 table which looks like:

<abc>

<abcdate>2007-01-31T13:47:27.25-05:00</abcdate>

</abc>

The following query :

SELECT xmlColumn.value('(/abc/abcDate)[1]', 'nvarchar(30)') FROM abcTABLE

Returns 2007-01-31T13:47:27.25-05:00

SELECT CAST(xmlColumn.value('(/abc/abcDate)[1]', 'nvarchar(30)') AS DATETIME) FROM abcTABLE

Returns

Msg 241, Level 16, State 1, Line 1

Conversion failed when converting datetime from character string.

--

SELECT CAST(xmlColumn.value('(/abc/abcDate)[1]', 'nvarchar(19)') AS DATETIME) FROM abcTABLE

Returns 2007-01-31 13:47:27.000

because length of 19 trims the milliseconds

Is it possible to convert this type of XML data type and still acheive accuracy to the milliseconds?

Thanks

Gary

You could use convert function with 126/127 style (for more info http://msdn2.microsoft.com/en-us/library/ms187928.aspx):

declare @.t1 varchar(25)

declare @.t2 varchar(25)

set @.t1 = '2007-01-31T13:47:27.25-05:00'

set @.t2 = '2006-12-12T23:45:12.10'

select convert(datetime,@.t2,126)

select convert(datetime,@.t1,127) --It must works, but don't work on my PC. I think something wrong with my system

|||

Hi Konstantin Kosinsky

It is not working at my machine as well, the "-" is the culprit still.

Style 127 is for ISO8601 with time zone Z: yyyy-mm-ddThh:mm:ss.mmmZ

It does not like the hyphen "-"

|||

Just quote from BOL:

The optional time zone indicator, Z, is used to make it easier to map XML datetime values that have time zone information to SQL Server datetime values that have no time zone. Z is the indicator for time zone UTC-0. Other time zones are indicated with HH:MM offset in the + or - direction. For example: 2006-12-12T23:45:12-08:00.

But it does not work. :).

At this moment i could propose substring by last "-" and use 126 or 127 style

|||

The docs are misleading here. If you look carefully at the table above the quote, style 127 only work with Zulu timezone ("Z"). The workaround here is to use XQuery simple type construction.

declare @.t1 xml

set @.t1 = '<abc>2007-01-31T13:47:27.25+05:00</abc>'

select @.t1.value('xs:dateTime(/abc[1])', 'datetime')

-galex

|||

Hi Galex

I tried:

SELECT

Assessmentxml.value('xs:dateTime(/Abc/AbcDate)[1]', 'datetime') AS [AssessmentExpectedStartDate]

FROM dbo.Assessment

I got:

Msg 2365, Level 16, State 1, Line 10

XQuery [dbo.Assessment.AssessmentXML.value()]: Cannot explicitly convert from 'xdt:untypedAtomic *' to 'xs:dateTime'

Then I tried:

SELECT

Assessmentxml.value('(/Abc/AbcDate)[1] CAST AS xs:dateTime', 'datetime') AS [AssessmentExpectedStartDate]

FROM dbo.Assessment

and got

Msg 2370, Level 16, State 1, Line 2

XQuery [dbo.Assessment.AssessmentXML.value()]: No more tokens expected at the end of the XQuery expression. Found 'CAST'.

What is the correct syntax to make it work?

Thanks

|||

In SQL Server, the expression you are casting (construction is the same) requires a singleton. Since you are using untyped XML, you need to use a positional predicate.

Please try:

SELECT

Assessmentxml.value('xs:dateTime(/Abc/AbcDate[1])', 'datetime') AS [AssessmentExpectedStartDate]

FROM dbo.Assessment

Notice the [1] predicate is inside the construction.

Also, XQuery is case sensitive, so you should be using "cast as" instead of "CAST AS".

-galex

|||

Hi Galex

I get the same error. Please try this:

set ansi_nulls, quoted_identifier, ansi_warnings, ansi_padding ON

declare @.Ax table

( AxID uniqueidentifier not null default(newid())

, AxRefDateTag sysname

, AxXML as cast(

'<Assessment xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<AssessmentID>' + cast(AxID as sysname) + '</AssessmentID>'

+ AxRefDateTag + '</Assessment>' as xml)

)

insert @.Ax(AxRefDateTag) values('<AssessmentReferenceDate>2007-01-31T13:47:27.25+05:00</AssessmentReferenceDate>')

SELECT

AxRefDateTag

,AxXml.value('xs:dateTime(/Assessment/AssessmentReferenceDate[1])', 'datetime') AS [AssessmentExpectedStartDate]

FROM @.Ax

|||

Gary,

That was my bad. I forgot to put keep the () around the path expression.

xs:dateTime((/Assessment/AssessmentReferenceDate)[1])

Keep in mind, this is also another option, but has different semantics:

xs:dateTime(/Assessment[1]/AssessmentReferenceDate[1])

Sorry for the confusion.

Regards,

Galex

|||This works well :) - thanks a lot Galex|||

Hi Gary

I am also getting the same issue.

Actually straightforward I cannot use the one which you sent ( 'xsateTime(/Assessment/AssessmentReferenceDate[1])', 'datetime')

I cannot say [1], i have to use the variable instead of that, because I am looping through the data.

I am using the below code. It is working fine when the date time is in this format 2007-01-31T13:47:27.25' only.

CAST(CAST(Message.query('data(//SHIPMENT/ISSUE_DT)[sql:variable("@.vcounter")]') AS VARCHAR) AS DATETIME) END,

I tried like this with the format you had given

Message.value('xsateTime((//SHIPMENT/ISSUE_DT)[sql:variable("@.vcounter")])', 'datetime'))

but i am getting an error while compiling

XQuery [Ediinbound.Message.value()]: Cannot explicitly convert from 'xdt:untypedAtomic *' to 'xsateTime'.

Kindly help me ASAP...

Thanks

Ram

|||

This is how it worked. Galex showed me how to put brackets around the XPATH.

set ansi_nulls, quoted_identifier, ansi_warnings, ansi_padding ON

declare @.Ax table

( AxID uniqueidentifier not null default(newid())

, AxRefDateTag sysname

, AxXML as cast(

'<Assessment xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<AssessmentID>' + cast(AxID as sysname) + '</AssessmentID>'

+ AxRefDateTag + '</Assessment>' as xml)

)

insert @.Ax(AxRefDateTag) values('<AssessmentReferenceDate>2007-01-31T13:47:27.25+05:00</AssessmentReferenceDate>')

SELECT * FROM @.Ax

SELECT

AxRefDateTag

,AxXml.value('xsateTime((/Assessment/AssessmentReferenceDate)[1])', 'datetime') AS [AssessmentExpectedStartDate]

FROM @.Ax

I haven't tried the variable before, but I think from the above explanation [1] or singleton is a MUST in SQL Server queries.