Storing Date Time in an XML column - XML format vs SQL Format

Question

I have a stored procedure, which has the parameters OrderID (bigint) and ApprovalDateTime(varchar(25))

The SP simply updates an XML column called UserDef with the following XML for a given OrderID:

<UserDef>
<ApprovalDateTime>01/01/2013 13:30:30</ApprovalDateTime>
</UserDef>

My question is, while storing a date time value like this in an XML column, what format is best?

1. The ISO 8601 XML format like 2002-05-30T09:00:00
2. a String like 01/01/2013 13:30:30 or
3. Unseparated format like 20130101 13:30:30
4. or any other format

Which one would you recommend?

This date will be extracted in another stored procedure for presenting in an SSRS report's column.

Answer 1

My first suggestion would be to fix the data type. There is absolutely no reason I can fathom that this single value should be stored with all that extra XML fluff around it. Leave the presentation stuff to the presentation tier.

Given that you are seemingly stuck with it for now, I would use ISO 8601 (the first option). Why?

1. Using mm/dd/yyyy (your option 2) is prone to misinterpretation in various ways, for example if you had 05/06/2013 how will I know if that's May 6th or June 5th? Will all of your audience like the same date format you chose? Do you even know? In fact to pinpoint why this is a problem, I don't even know if you meant mm/dd/yyyy or dd/mm/yyyy.

2. The unseparated format is slightly more cumbersome to read, especially when you have dates with consecutive characters, e.g. 20110110. The dashes can be a great visual separator, just don't ever forget the T:

   SET LANGUAGE FRENCH;
   SELECT CONVERT(DATETIME, '2013-05-06 13:30:30');
   

   Result (June 5th!):

   2013-06-05