Problems with XML Date Time Conversion

  • 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

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

    😎

  • 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]

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

  • 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)

  • 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

  • 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]

  • 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);

  • 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]

  • 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

    😎

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

    Jordon

  • 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