How to convert a string to datetime using T-SQL?

Question

Hi all i'm trying to parse an xml file in SQl Server 2005. I have a datetime field in the xml which is a string as follows: '20110805060028.387'. I want to convert this to a datetime field in sql server, but i'm struggling with the syntax. Is it possible to get dd/mm/yyy hh:mm:ss.ms (ms = micro seconds) from this string?

Answer 1

Its not a supported format so you will need to;

declare @dt varchar(42)= '20110805060028.387'
--convert to '20110805 06:00:28.387'
select cast(stuff(stuff(stuff(@dt, 9, 0, ' '), 12, 0, ':'), 15, 0, ':') as datetime)

>>2011-08-05 06:00:28.387

Answer 2

Substring is your friend here:

DECLARE @Value NVarChar(50)
SET @Value = '20110805060028.387'
SELECT Convert(DateTime, 
    SubString(@Value, 1, 4) + '-' + 
    SubString(@Value, 5, 2) + '-' + 
    SubString(@Value, 7, 2) + ' ' + 
    SubString(@Value, 9, 2) + ':' + 
    SubString(@Value, 11, 2) + ':' + 
    SubString(@Value, 13, 10))

(by the way, I am purposefully NOT following your "dd/mm/yyy" format when creating a parseable date string here, but rather an international (not quite ISO-standard) format that SQL server will read correctly regardless of the server's DATEFORMAT settings)