Skip to content

Oracle date and timestamp formatting

In Oracle databases, the display format of dates and timestamps is controlled by session parameters. To prevent garbled or unreadable output, these parameters can be manually configured to ensure consistency.^[600-developer__database__oracle__oracle-record.md]

Formatting Parameters

The NLS_DATE_FORMAT parameter specifies the format for displaying DATE values, while NLS_TIMESTAMP_FORMAT handles TIMESTAMP values, including fractional seconds.^[600-developer__database__oracle__oracle-record.md]

SQL Commands

You can modify the format for the current session using the ALTER SESSION command.

Set Date Format: 

alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
This formats dates as Year-Month-Day Hour:Minute:Second (24-hour clock).^[600-developer__database__oracle__oracle-record.md]

Set Timestamp Format: 

alter session set NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS:FF6';
This formats timestamps to include 6 digits of fractional seconds (microseconds).^[600-developer__database__oracle__oracle-record.md]

Sources