CODEWITHSUNDEEP
sql-serverxmldatetime
Bat_Programmer
Bat_Programmer

Reputation: 6851

How to convert XML string value to datetime in SQL Server

I have below code which selects a date value from an XML string in SQL Server. The string value returns format in ddmmyy and I need to convert this to T-SQL datetime type before saving it to a table.

SELECT TOP 1
    list.n.value('(DOB/@value)[1]', 'datetime') AS 'DOB'
FROM 
    @ResultXML.nodes('/Variables') AS list(n)

XML file:

<Variables>
    <DOB>111290</DOB>
</Variables>

Upvotes: 1

Views: 4310

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

You might try it like this:

DECLARE @XML XML=
'<Variables>
    <DOB>111290</DOB>
</Variables>';

SELECT CONVERT(DATETIME,STUFF(STUFF(@XML.value('(/Variables/DOB)[1]','varchar(max)'),3,0,'/'),6,0,'/'),3)

First you use two times STUFF to get 11/12/90 instead of 111290, than you use the 3 to convert this to datetime (or any other fitting format: use . for german, - for british...) More details on CAST and CONVERT

Best was, to store date and time values properly. Within XML this should be ISO8601, which means yyyy-MM-dd or yyyy-MM-ddThh:mm:ss More details on ISO8601

Upvotes: 2

John Cappelletti
John Cappelletti

Reputation: 81930

If SQL 2012+

The example 

Declare @String varchar(25)= '111290'
-- If MMDDYY
Select DATEFROMPARTS(iif(Right(90,2)>25,'19'+Right(90,2),'20'+Right(90,2)),Left(@String,2),Substring(@String,3,2))

-- If DDMMYY
Select DATEFROMPARTS(iif(Right(90,2)>25,'19'+Right(90,2),'20'+Right(90,2)),Substring(@String,3,2),Left(@String,2))

Returns:  1990-11-12
Returns:  1990-12-11

Upvotes: 0

Related Questions