0

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.

FMFF
  • 1,652
  • 4
  • 32
  • 62
  • 4
    How about you cut the XML out and use a datetime? – billinkc Sep 03 '13 at 16:22
  • 4
    Why not just store an actual `DateTime` value in an actual `DateTime` column? You can always add the XML fluff later, and not worry about format in the database. The database is supposed to store *data*; the client is the only layer that should know or care about *formatting*. – Aaron Bertrand Sep 03 '13 at 16:23
  • 1
    Why not use the presentation format for the report? I suggest that you make your ApprovalDateTime parameter a datetime instead of a varchar. – Dan Bracuk Sep 03 '13 at 16:24
  • @AaronBertrand: I agree with that, but because of the complexities involved like replication, mirroring, BizTalk etc, adding a column or changing the column type is not an option. This is a requirement for one client out of several hundreds who all share the same table. This is why we are using the existing 'catch-all' column UserDef in this table instead of adding a new column called ApprovalDateTime. – FMFF Sep 03 '13 at 16:28

1 Answers1

2

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
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490