Problems with XML Date Time Conversion

Question

Post reply

Problems with XML Date Time Conversion

jordon.shaw

Hall of Fame

Points: 3571
More actions
May 20, 2015 at 1:14 pm

#304126

Okay, I'm banging my head against the wall on this one. I am querying XML data, which the data and time is returning in this format:

2015-01-16T16:06:14.577-06:00

This is my query:

SELECT CONVERT(XML,BUSINESSOBJECTIMAGE).value('(NewDataSet/Table1/InstalledDate)[1]', 'nvarchar(100)') AS 'Installed Date' FROM CORE_AUDITINGTRAIL.AUDITDETAIL

Running that, I get the native Date Time format, as I pointed out above. So, I've tried multiple ways to convert that into SQL format. I tried:

SELECT CONVERT(XML,BUSINESSOBJECTIMAGE).value('xs:dateTime((NewDataSet/Table1/InstalledDate)[1])', 'nvarchar(100)') AS 'Installed Date' FROM CORE_AUDITINGTRAIL.AUDITDETAIL

This doesn't seem to make a difference and still gives me the date in native XML format as identified above. So, then I tried this:

SELECT CONVERT(XML,BUSINESSOBJECTIMAGE).value('xs:dateTime((NewDataSet/Table1/InstalledDate)[1])', 'datetime') AS 'Installed Date' FROM CORE_AUDITINGTRAIL.AUDITDETAIL

Now I get an error:

Msg 242, Level 16, State 3, Line 1

The conversion of a datetimeoffset data type to a datetime data type resulted in an out-of-range value.

So, then I try converting it:

SELECT CONVERT(datetime,CONVERT(XML,BUSINESSOBJECTIMAGE).value('xs:dateTime((NewDataSet/Table1/InstalledDate)[1])', 'nvarchar(100)')) AS 'Installed Date' FROM CORE_AUDITINGTRAIL.AUDITDETAIL

Then I get this error:

Msg 241, Level 16, State 1, Line 1

Conversion failed when converting date and/or time from character string.

So, then I try converting it with 127 datetime type and get the same error:

SELECT CONVERT(datetime,CONVERT(XML,BUSINESSOBJECTIMAGE).value('xs:dateTime((NewDataSet/Table1/InstalledDate)[1])', 'nvarchar(100)'),127) AS 'Installed Date' FROM CORE_AUDITINGTRAIL.AUDITDETAIL

I even tried to CAST it as a datetime. At this point, I'm lost. Any help on this would be greatly appreciated!

Jordon

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 1

Eirikur Eiriksson

SSC Guru

Points: 182631

May 20, 2015 at 1:21 pm

#1799452

Quick thought, should be 21/121 for datetime/datetime2 + offset

😎

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 2

Looks like the style is 127. Did you try convert specifying style 127?

https://technet.microsoft.com/en-us/library/ms187928%28v=sql.110%29.aspx

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

jordon.shaw Hall of Fame Points: 3571 More actions · Answer 3

jordon.shaw

Hall of Fame

Points: 3571

May 20, 2015 at 1:25 pm

#1799455

I did. In one of my queries above, I have specified 127 and I still got an error.

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 4

Eirikur Eiriksson

SSC Guru

Points: 182631

May 20, 2015 at 1:31 pm

#1799456

Just realized that the target data type must be DATETIMEOFFSET, otherwise it will throw errors in any conversion

😎

USE tempdb;



GO

SET NOCOUNT ON;

DECLARE @MY_TIME VARCHAR(100) = '2015-01-16T16:06:14.577-06:00';

SELECT CONVERT(DATETIMEOFFSET,@MY_TIME,127)

jordon.shaw Hall of Fame Points: 3571 More actions · Answer 5

Eirikur Eiriksson (5/20/2015)

Just realized that the target data type must be DATETIMEOFFSET, otherwise it will throw errors in any conversion

😎

USE tempdb;
GO SET NOCOUNT ON; DECLARE @MY_TIME VARCHAR(100) = '2015-01-16T16:06:14.577-06:00';
SELECT CONVERT(DATETIMEOFFSET,@MY_TIME,127)

That didn't throw any errors, but the output is still not what I'm looking for. I'm now getting:

2015-02-20 00:00:00.0000000 -06:00

And I want to get:

2015-02-20 00:00:00.000

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 6

SELECT CONVERT(DATETIME2, '2015-01-16T16:06:14.577-06:00', 127) results in: 2015-01-16 16:06:14.5770000

Does this help?

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 7

Eirikur Eiriksson

SSC Guru

Points: 182631

May 20, 2015 at 1:37 pm

#1799459

Just one step left

😎

USE TEMPDB;



GO

SET NOCOUNT ON;

DECLARE @MY_TIME VARCHAR(100) = '2015-01-16T00:00:00.000-06:00';

SELECT CONVERT(DATETIMEOFFSET,@MY_TIME,127);

SELECT CONVERT(DATETIME,CONVERT(DATETIMEOFFSET,@MY_TIME,127),0);

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 8

Eirikur Eiriksson (5/20/2015)

Just one step left

😎

USE TEMPDB;
GO SET NOCOUNT ON; DECLARE @MY_TIME VARCHAR(100) = '2015-01-16T00:00:00.000-06:00'; SELECT CONVERT(DATETIMEOFFSET,@MY_TIME,127);
SELECT CONVERT(DATETIME,CONVERT(DATETIMEOFFSET,@MY_TIME,127),0);

Were you looking over my shoulder? 😉

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 9

Alvin Ramard (5/20/2015)

Eirikur Eiriksson (5/20/2015)

Just one step left

😎

USE TEMPDB;
GO SET NOCOUNT ON; DECLARE @MY_TIME VARCHAR(100) = '2015-01-16T00:00:00.000-06:00'; SELECT CONVERT(DATETIMEOFFSET,@MY_TIME,127);
SELECT CONVERT(DATETIME,CONVERT(DATETIMEOFFSET,@MY_TIME,127),0);

Were you looking over my shoulder? 😉