How to convert XML string value to datetime in SQL Server

Question

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>

Possible duplicate of [Convert varchar into datetime in SQL Server](http://stackoverflow.com/questions/1509977/convert-varchar-into-datetime-in-sql-server) — artm, Jun 22 '16 at 23:41

score 2 · Accepted Answer · answered Jun 23 '16 at 06:57

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

Thanks. I got the answer from following link similar to yours: http://stackoverflow.com/a/13512238/475709 — Bat_Programmer, Jun 23 '16 at 22:59

score 0 · Answer 2 · answered Jun 23 '16 at 00:15

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

How to convert XML string value to datetime in SQL Server

2 Answers2