I’ve never been one for formatting data in the database, it’s something I’ve always done when the data is being displayed. Some work I’ve been doing recently has further re-enforced this view.
See, while trying to reverse engineer some old code, I happened to notice some odd data. The data was an XML document generated within a stored procedure in Oracle. This should not be a big issue as the Xml should only be holding data, after all, Xml is really just a data storage/transport format. This generated Xml document is then used as a data source for a mail merge. I’m sure you can see where this is heading, the data in the Xml gets formatted instead of doing the formatting in the merge document.
One of the dates in the XML document needs to be formatted like “01 January 2010”. So, the date value is passed into the oracle to_char(datevalue, format) function with the format specified as “dd Month YYYY”. On the surface this all looks fine, except that the data coming out looks a little strange. There are extra spaces between the month and the year.
After a bit of playing and a few seconds using my favourite search engine, and I found the answer. “Month” returns the full name of the month padded to 9 characters. Yep, it’s fixed width. There is no option for a non-fixed width full month format. You can do the abbreviated version (3 chars) or the full at 9.
I full understand that this isn’t something that can just be changed, but it would be nice (we are in 2010) to be able to format a date in oracle without the padding.
Well, that’s the end of my little story and mini rant.
