How can I reduce the width of column in Oracle (SQL Plus) with TIMESTAMP datatype?

Question

In SQL Plus output it is taking up much more space than it needs and I'd like to reduce it from say 50 chars to 20.

Answer 1

If you reduce the width to 20 it won't be wide enough for the default TIMESTAMP or TIMESTAMP WITH TIME ZONE format. When that happens, SQLPlus will wrap the value.

Assume table b has timestamp column TS:

COLUMN ts FORMAT A20
SELECT ts FROM b;

TS
--------------------
25-APR-13 11.28.40.1
50000 AM

To cut down the width even further, decide which information you want and format accordingly. The Oracle DateTime and Timestamp formatting codes are listed here.

Note that SQLPlus won't let you specify a date format with the COLUMN statement. That's why I used FORMAT A20 above. You can get it to 19 characters if you drop fractional seconds and use a 24-hour clock instead of AM/PM, and drop the time zone:

COLUMN TSFormatted FORMAT A20
SELECT TO_CHAR(ts, 'MM/DD/YYYY HH24:MI:SS') AS TSFormatted FROM b;

TSFORMATTED
--------------------
04/25/2013 11:28:40

If you're willing to drop the century you can get two of the fractional seconds and an exact width of 20:

COLUMN TSFormatted FORMAT A20
SELECT TO_CHAR(ts, 'MM/DD/YY HH24:MI:SS.FF2') AS TSFormatted from b;

TSFORMATTED
--------------------
04/25/13 11:28:40.15

Finally, if you want all of your timestamps to be automatically formatted in a certain way, use ALTER SESSION:

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'MM/DD/YY HH24:MI:SS.FF2';
COLUMN ts FORMAT A20
SELECT ts from b; -- don't need to_char because of the default format

TS
--------------------
04/25/13 11:28:40.15