Menu

How to Change the Date Format in Your Oracle Session

Learn how to change the date and timestamp display formats in an Oracle session using ALTER SESSION SET NLS_DATE_FORMAT, query current settings, and format dates safely.

Posted on By
On this page

When a client displays Oracle DATE and timestamp values as text, the session’s National Language Support (NLS) format parameters can affect that display. The configured formats vary by database, session, and client. You can inspect the current session values with NLS_SESSION_PARAMETERS before changing them.

In this guide, you will learn how to change the date and timestamp format for your current Oracle session using ALTER SESSION, how to inspect your current NLS settings, and when to prefer explicit formatting with TO_CHAR().

Changing the Date Format with ALTER SESSION

The standard way to change the default date display format for the duration of your connection is the ALTER SESSION SET NLS_DATE_FORMAT command:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

Once executed in your current session, any subsequent query that outputs a DATE value without explicit formatting uses this new pattern:

SELECT SYSDATE FROM dual;

Output:

SYSDATE
-------------------
2024-04-03 14:35:10

Common Date Format Patterns

You can combine standard Oracle date format model elements to match your requirements:

Format Pattern Example Output Description
'YYYY-MM-DD' 2024-04-03 ISO 8601 calendar date
'YYYY-MM-DD HH24:MI:SS' 2024-04-03 14:35:10 Full date and 24-hour time
'MM/DD/YYYY HH:MI:SS AM' 04/03/2024 02:35:10 PM US standard format with 12-hour clock
'DD-MON-YYYY' 03-APR-2024 Abbreviated month with 4-digit year

Changing Timestamp Formats

Oracle distinguishes between DATE, TIMESTAMP, and TIMESTAMP WITH TIME ZONE data types. Setting NLS_DATE_FORMAT affects only values of type DATE. To customize timestamps, update NLS_TIMESTAMP_FORMAT and NLS_TIMESTAMP_TZ_FORMAT:

-- Format TIMESTAMP columns (including fractional seconds .FF)
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF3';

-- Format TIMESTAMP WITH TIME ZONE columns (including timezone offset TZH:TZM)
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF3 TZH:TZM';

Now, query LOCALTIMESTAMP and SYSTIMESTAMP:

SELECT LOCALTIMESTAMP, SYSTIMESTAMP FROM dual;

Output:

LOCALTIMESTAMP             SYSTIMESTAMP
-------------------------- ---------------------------------
2024-04-03 14:35:10.123    2024-04-03 14:35:10.123 +00:00

How to Check Your Current Session Date Format

You can inspect the active NLS parameters for your current session by querying the nls_session_parameters view:

SELECT parameter, value
FROM nls_session_parameters
WHERE parameter IN ('NLS_DATE_FORMAT', 'NLS_TIMESTAMP_FORMAT', 'NLS_TIMESTAMP_TZ_FORMAT');

Output:

PARAMETER                  VALUE
-------------------------- ---------------------------------
NLS_DATE_FORMAT            YYYY-MM-DD HH24:MI:SS
NLS_TIMESTAMP_FORMAT       YYYY-MM-DD HH24:MI:SS.FF3
NLS_TIMESTAMP_TZ_FORMAT    YYYY-MM-DD HH24:MI:SS.FF3 TZH:TZM

Best Practice: Explicit Formatting with TO_CHAR()

While ALTER SESSION is convenient for ad-hoc debugging in tools like SQL*Plus, SQL Developer, or DBeaver, production applications should never rely on session-level NLS defaults. Different client libraries, connection pools, and database users can have varying NLS configurations, potentially breaking application string parsing or date comparisons.

For production SQL queries and reports, always use TO_CHAR() with an explicit format model:

SELECT
    order_id,
    TO_CHAR(order_date, 'YYYY-MM-DD HH24:MI:SS') AS formatted_order_date
FROM orders;

Explicit formatting guarantees consistent, deterministic output regardless of the client’s locale or session settings.

  • SYSDATE: Returns the current database server date and time.
  • CURRENT_DATE: Returns the current date in the session time zone.
  • SYSTIMESTAMP: Returns the database server’s system timestamp with fractional seconds and timezone.
  • LOCALTIMESTAMP: Returns the current session timestamp without time zone.
  • LAST_DAY(): Computes the last day of the month for a given date.
  • FROM_TZ(): Converts a timestamp and timezone into a TIMESTAMP WITH TIME ZONE.

Conclusion

The ALTER SESSION SET NLS_DATE_FORMAT statement provides a fast, session-scoped mechanism to inspect and display Oracle dates in a readable format. For timestamp types, remember to adjust NLS_TIMESTAMP_FORMAT and NLS_TIMESTAMP_TZ_FORMAT. In application queries, always pair dates with TO_CHAR() to ensure robust, reproducible formatting across all environments.