XML DATETIME CONVERSION FAILING TO CONVERT TO SQL DATETIME

• Gagandeep Singh

  SSCommitted

  Points: 1513

  More actions

  March 6, 2007 at 3:19 pm

  #92205

  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

   

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  March 7, 2007 at 5:16 pm

  #693699

  You will have to remove the UTC offset ('-05:00') before converting to datetime.

  select DATE = convert(datetime,substring('2007-01-31T13:47:27.25-05:00',1,22))

  DATE                                                   
  ------------------------------------------------------ 
  2007-01-31 13:47:27.250

  (1 row(s) affected)