Skip to content

Oracle NLS Date and Timestamp Formatting

Oracle NLS (National Language Support) parameters control the format of date and time data within an Oracle session. By default, dates may appear in an unclear format or display as garbled characters if the client environment is mismatched.^[600-developer-database-oracle-oracle-record.md]

Date Formatting

The display format for the DATE data type is managed by the NLS_DATE_FORMAT parameter.^[600-developer-database-oracle-oracle-record.md] You can modify this setting for the current session using the ALTER SESSION command.^[600-developer-database-oracle-oracle-record.md]

For example, to display dates including the time (hours, minutes, and seconds), you can execute the following:

alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Timestamp Formatting

The TIMESTAMP data type, which stores fractional seconds, is formatted using the NLS_TIMESTAMP_FORMAT parameter.^[600-developer-database-oracle-oracle-record.md] Similar to the date format, this must be set to properly interpret or display the precision of the timestamp data.^[600-developer-database-oracle-oracle-record.md]

The following SQL statement sets the format to include fractional seconds up to 6 digits of precision:

alter session set NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS:FF6';
  • [[SQL]]
  • [[Oracle Database]]

Sources

  • 600-developer-database-oracle-oracle-record.md