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 12 (of 12 total)

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

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

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

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

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

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: 182947 More actions · Answer 7

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: 182947 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?

As always Alwin:-D

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

Thank you both for your help! Worked perfectly!!!

Jordon

Eirikur Eiriksson SSC Guru Points: 182947 More actions · Answer 11

jordon.shaw (5/20/2015)
Thank you both for your help! Worked perfectly!!!
Jordon

You are most welcome.

Problems with XML Date Time Conversion

Cookies on SQLServerCentral