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?
Add a comment
|
4 Answers
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
1 Comment
amantur
I don't forget stuff, but should not have been able to accomplish this. Wonderful answer as always!
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)
DECLARE @parsedValue VARCHAR(MAX)
DECLARE @rawDateValue VARCHAR(MAX)
SET @rawDateValue = '20110805060028.387'
SET @parsedValue = SUBSTRING(@rawDateValue, 1, 4) + '/' +
SUBSTRING(@rawDateValue, 5, 2) + '/' +
SUBSTRING(@rawDateValue, 7, 2) + ' ' +
SUBSTRING(@rawDateValue, 9, 2) + ':' +
SUBSTRING(@rawDateValue, 11, 2) + ':' +
SUBSTRING(@rawDateValue, 13, 2) + ':' +
SUBSTRING(@rawDateValue, 16, 3)
SELECT CONVERT(datetime, @parsedValue)
Comments
My take (necessarily not the best):
DECLARE @val VARCHAR(30)='20110805060028.387'
SELECT LEFT(@val,4) [yyyy], RIGHT(LEFT(@val,6),2)[mm], RIGHT(LEFT(@val,8),2)[dd],
RIGHT(LEFT(@val,10),2)[hh], RIGHT(LEFT(@val,12),2)[mi],
RIGHT(LEFT(@val,14),2)[ss],SUBSTRING(@val, 15,LEN(@val)-14) [ms]
DECLARE @date DATETIME
SELECT @date = CAST(LEFT(@val,4) + '-' + RIGHT(LEFT(@val,6),2) + '-' +
RIGHT(LEFT(@val,8),2) + ' ' + RIGHT(LEFT(@val,10),2) + ':' +
RIGHT(LEFT(@val,12),2) + ':' + RIGHT(LEFT(@val,14),2) +
SUBSTRING(@val, 15,LEN(@val)-14) AS DATETIME)
SELECT @date, DATEPART(MILLISECOND,@date)[ms], DATEPART(MONTH,@date)[MM]
