Oracle PL/SQL Programming, 5th Edition

Appendix C. Date Format Models

Table C-1 lists the date format model elements that you can use with the conversion functions TO_CHAR, TO_DATE, TO_TIMESTAMP, and TO_TIMESTAMP_TZ. Some of the model elements in Table C-1 are also used with ROUND and TRUNC.

You have the option of specifying default date and timestamp formats at the session level, a capability that can come in handy if your particular needs differ from those of the majority of database users. Use the ALTER SESSION command to specify session-level default date and timestamp formats. The following example works in Oracle8i Database or higher, and sets the default date format to MM/DD/YYYY:

BEGIN
   EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''MM/DD/YYYY''';
END;

To check the default date format in effect for your session at any given time, issue the following query against the NLS_SESSION_PARAMETERS data dictionary view:

SELECT value
FROM nls_session_parameters
WHERE parameter='NLS_DATE_FORMAT';

To set or check default timestamp formats, use NLS_TIMESTAMP_FORMAT and NLS_TIMESTAMP_TZ_FORMAT.

Some elements in Table C-1 apply only when translating datetime values from Oracle’s internal format into character strings, and not vice versa. Such elements can’t be used in a default date model (e.g., with NLS_DATE_FORMAT) because the default date model applies to conversions in both directions. These elements are noted as “Output-only” in the table.

Table C-1. Date format model elements

Element	Description
Other text	Any punctuation, ...