May 20, 2015 at 1:14 pm
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
May 20, 2015 at 1:21 pm
Quick thought, should be 21/121 for datetime/datetime2 + offset
😎
May 20, 2015 at 1:22 pm
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
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]
May 20, 2015 at 1:25 pm
I did. In one of my queries above, I have specified 127 and I still got an error.
May 20, 2015 at 1:31 pm
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)
May 20, 2015 at 1:34 pm
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
May 20, 2015 at 1:36 pm
SELECT CONVERT(DATETIME2, '2015-01-16T16:06:14.577-06:00', 127) results in: 2015-01-16 16:06:14.5770000
Does this help?
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]
May 20, 2015 at 1:37 pm
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);
May 20, 2015 at 1:39 pm
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? 😉
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]
May 20, 2015 at 1:40 pm
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
😎
May 20, 2015 at 1:41 pm
Thank you both for your help! Worked perfectly!!!
Jordon
May 20, 2015 at 1:42 pm
jordon.shaw (5/20/2015)
Thank you both for your help! Worked perfectly!!!Jordon
You are most welcome.
😎
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply