Toad World® Forums

Output Formatting

This is a two part question :slight_smile:

I have a report that, as part of the output, contains dates. When I export to Excel from within TDA, the date is formatted as MM/DD/YYYY - exactly as I want it. When TDA Automation creates the report, the date is formatted as MM/DD/YYYY HH:mm:ss - which, while not incorrect, is annoying my user and thus they are annoying me. Is there a reason why the Automation export to Excel does not format data the same was as the Quick Export to Excel from within TDA? I’ve also noticed something else regarding Quick Export vs. Automation Export - Quick Export auto-sizes columns to Data, the Automation export does not, even if the option is selected to do so in the Automation script.

Edited to add: I can use to_char to format the date as I want it - but when I do so, my ORDER BY expression no longer works on that field - I get the error message " Lookup Error - Oracle Database Error: ORA-01791: not a SELECTed expression"

The second question:

I have a report (different user, same level of annoyance) that contains a field containing a monetary amount. Is there a way to export this field so that it’s formatted as (at least) XXX.XX if not $XXX.XX? I know I can concatenate the $ to the front of the field (’$’||C.MONEY), but it’s the trailing decimal that I’m having issues with, since if the last decimal is a 0, it gets dropped from the output (thus $2.90 becomes 2.9).

Thanks :slight_smile:

If you are using Quick Export to an instance of Excel it is a little different. In this circustance we are using the Microsoft Comm object. When exporting to a file, through the Export wizard or Automation, we are using a different component and have to do things slightly different.

All of these differences can be resolved through formatting the column in your SQL statement.

For the currency issue you can try this. (This is Oracle so if you are using other db you will have to see what they have available)

to_char(RETAIL_PRICE, ‘$9,999.00’)

For date

to_char(MYDATE, ‘MM/DD/YYY’)