Converting XML Datetime to SQL datetime

• Gagandeep Singh

  SSCommitted

  Points: 1513

  More actions

  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

• peterhe

  SSChampion

  Points: 11469

  More actions

  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.

• Jeff Moden

  SSC Guru

  Points: 1004405

  More actions

  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)