Formatting Date Columns in Oracle SQL*Plus

Date display in Oracle AI Database 26ai works on two layers, and it is worth separating them clearly before diving into examples. The first layer is the actual date format: what characters and order SQL*Plus uses to display a DATE value at all, controlled by the NLS_DATE_FORMAT parameter. The second layer is column width and heading: how much horizontal space that formatted text occupies on your report, controlled by the SQL*Plus COLUMN command, exactly as covered for text columns in Lesson 4. Both layers matter, and confusing them is the most common source of frustration when a date column does not display the way you expect.

An unformatted DATE column does not display in some fixed, universal format. Its default format and default width are both derived from the NLS_DATE_FORMAT parameter, which itself follows the NLS territory setting for your session. For the America territory, the default format is DD-Mon-RR, using a two-digit, century-aware year, with a default width of A9. If you have never explicitly set NLS_DATE_FORMAT yourself, this is the format governing every unformatted DATE column you see.

You can change this for your entire session with a single command:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

From that point forward, every unformatted DATE value in the session displays using this new format, with no TO_CHAR required at all. This is genuinely useful when you want a consistent date format across an entire script or reporting session without repeating a format model in every single query.

The other approach, and the more portable one, is converting a date to a specific format directly inside your SELECT statement using TO_CHAR. This is the right choice when you want a particular format that travels with the query itself, independent of whatever the session's NLS_DATE_FORMAT happens to be set to at the time. The following example converts today's date so it displays in day-month-year format:

SQL> COLUMN todays_date FORMAT A11
SQL> SELECT TO_CHAR(SYSDATE, 'DD-Mon-YYYY') todays_date
  2  FROM   dual;

TODAYS_DATE
-----------
27-Aug-2026

Notice that the SELECT statement specifies a column alias of todays_date, and that the name used in the COLUMN command matches that alias exactly. This match is essential to making the example work at all. If you omit the column alias, Oracle generates a name based on the expression itself, typically something unwieldy derived from the function call, making it difficult to write a COLUMN command that reliably targets it.

DATE is not the only datetime type you will encounter. Oracle also supports TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE, each carrying more precision or time zone awareness than a plain DATE. All three follow the same two-layer approach covered above: their default display format comes from NLS_TIMESTAMP_FORMAT rather than NLS_DATE_FORMAT, and you can override it the same way:

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';

Column width and heading, once the format is settled, work identically to DATE columns: an explicit COLUMN FORMAT An clause, sized to whatever the formatted output actually needs.

When displaying any of the datatypes covered in this lesson, you can either accept SQL*Plus's default display width or change it with the COLUMN command. A format model stays in effect until you enter a new one, reset it explicitly with:

COLUMN column_name CLEAR

or exit SQL*Plus entirely. The datatypes this applies to include:

1. CHAR
2. NCHAR
3. VARCHAR2 (VARCHAR)
4. NVARCHAR2 (NCHAR VARYING)
5. DATE, TIMESTAMP, and the TIMESTAMP time zone variants
6. LONG
7. BLOB, BFILE
8. CLOB
9. NCLOB
10. XMLType

The default width of most datatype columns is simply the width of the column as defined in the database. Large object types work a little differently: a LONG, BLOB, BFILE, CLOB, NCLOB, or XMLType column's default width comes from whichever is smaller, SET LONGCHUNKSIZE or SET LONG. As already established, unformatted DATE and TIMESTAMP columns get their default width and format from the relevant NLS format parameter, falling back to A9 for DATE if nothing else applies. Left justification is the default for all of these datatypes.

To change the displayed width of any datatype, including DATE, use the COLUMN command with a format model built from the letter A, for alphanumeric, followed by a number representing the desired width in characters:

COLUMN column_name FORMAT model

If you specify a width shorter than the column heading itself, SQL*Plus truncates the heading to fit, so it is worth choosing a width that comfortably fits both the data and whatever heading text you have set.

The table below matches several format models to the output they actually produce, corrected against confirmed current behavior:

Two corrections from earlier versions of this table are worth calling out directly. The dollar-sign row now shows $9,999.99 as the format model rather than a version containing stray literal digits that do not belong in a format template at all. And the negative-value example at the bottom now uses the PR clause, confirmed in the previous lesson to display negative values in angle brackets, rather than showing literal parenthesis characters written directly into the format model, which is not a demonstrated, reliable way to produce parenthesized output.

To restate the key fact plainly: the default date format in SQL*Plus comes from the database's NLS_DATE_FORMAT parameter, and for many common NLS territories, including America, that default format displays a two-digit year rather than a four-digit one. You can use TO_CHAR, or an ALTER SESSION SET NLS_DATE_FORMAT command, to control exactly how dates display in your reports; which one you reach for depends on whether you want the format to apply just to one query or to your whole session.

The default width of CHAR, NCHAR, VARCHAR2, and NVARCHAR2 columns is the width of the column in the database, and SQL*Plus formats all of these left-justified. If a value does not fit the column width, SQL*Plus wraps or truncates it depending on the setting of SET WRAP, exactly as covered in Lesson 4.

For large object types specifically, a LONG, BLOB, BFILE, CLOB, NCLOB, or XMLType column's width defaults to whichever is smaller, SET LONGCHUNKSIZE or SET LONG. SQL*Plus truncates or wraps XMLType columns after 2000 bytes by default; to avoid this, set an explicit COLUMN format for the XMLType column yourself. A COLUMN format, for any datatype, can be up to a maximum of 60,000 characters per row.