DATETIME in XML

Question

I am trying capture a DATE based on a INT value in an xml variable.The xml variable will be used to filter some data in where condition.

SELECT  TOP 1 DATEADD(MINUTE,15,DATEADD(HOUR,11,CONVERT(DATETIME, CONVERT(VARCHAR(10),20120730), 101))) AS CurrentDate
FROM TABLE1 T
FOR XML AUTO,ELEMENTS OPTION(FAST 1)

The above query gives me the datetime in XML variable but there is a T inserted between DATE and TIME. Now when I use the variable CurrentDate to filter data further down in the same XML file, the XML throws an error  - Incorrect Syntax near 11. I believe this because of T in the variable. Any pointers to solve this would be appreciated. Here is the query that is being used  -

SELECT ABC FROM TABLE2
WHERE CreatedDate = MSXT__//T/CurrentDate__MSXT

 

Answer 1

Try:

SELECT  TOP (1) DATEADD(MINUTE,15,DATEADD(HOUR,11,CONVERT(DATETIME, CONVERT(VARCHAR(10),20120730)))) AS CurrentDate
FROM TABLE1 T
FOR XML AUTO,ELEMENTS OPTION (FAST 1)

This worked fine for me. You used wrong code for CONVERT, it should be 112 instead of 101 or no code at all.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

I might be missing something here, but did not work for me. Found this -

http://www.dbforums.com/microsoft-sql-server/1658395-datetime-values-when-using-xml-path.html

This explains why this happens.

Answer 3

I've tried your code in SQL 2012 where it worked fine. May be I should try in SQL 2008 R2. What is your version?

Actually, I re-read your question. You're saying that the T in the middle gives you troubles. I do have T in the middle also.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 4

I am using SQL 2008 R2. I believe the T in the middle is the cause for the problem. I am not 100% sure though.