Post reply

Converting XML Datetime to SQL datetime

  • March 6, 2007 at 3:20 pm

    #175505

     

    I have an XML colmn 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

  • March 9, 2007 at 8:00 am

    This was removed by the editor as SPAM

  • March 14, 2007 at 7:04 am

    #694915

    SQL server does not support time zone when cpnverting xml dateTime data type.

    If you truncate the time zone part from the date time string (-5:00), the converted data time will have the milliseconds.

    Time zone part may or may not exist in xml dateTime data type.

  • March 14, 2007 at 4:24 pm

    #695095

     SELECT CAST(LEFT(@TestXMLTime,22) AS DATETIME) works just fine...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 4 posts - 1 through 3 (of 3 total)

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